Re: [postgis-users] How to reproject data with custom projection?
Aren, You need a reference projection, you are correct. For the sake of the exercise, bookmark http://spatialreference.org/ and then try the search box for parts of the string which are maybe unusual : I tried "Standard_Parallel_1",27.5 Lambert_Conformal_Conic in the search box. then I reckon you can probably choose any of the three results I get - there appears to be very little difference between them and your string. If you haven't used this site before, looking at the Well Known Text as HTML Human-Readable OGC WKT Proj4 OGC WKT JSON GML ESRI WKT .PRJ File options will help. Good luck (from the antipodes, so take your chances) cheers Ben On 09/01/2011, at 3:18 PM, Aren Cambre wrote: > I have a SHP with this in its PRJ file: > > PROJCS["Custom",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",150.0],PARAMETER["False_Northing",500.0],PARAMETER["Central_Meridian",-100.0],PARAMETER["Standard_Parallel_1",27.5],PARAMETER["Standard_Parallel_2",35.0],PARAMETER["Central_Parallel",18.0],UNIT["Meter",1.0]] > > I need to get this into 3081 somehow since it's a good projection for Texas. > > I have some 3081-reprojected PoistGIS data (originally 4269) for Texas > already. If I load this SHP into QGIS along with other 3081 data, it appears > well below the other data. QGIS indicates this SHP has a custom projection. > > If I am not mistaken, to reproject with PostGIS, I need something besides -1 > in the geometry table. So I guess I need to specify some SRID with -s when I > run shp2pgsql? If so, what projection is this? > > Thanks, and I apologize in advance for my naivety. > > Aren > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to reproject data with custom projection?
Plug that WKT into http://prj2epsg.org and the answer appears to be EPSG:3082 http://prj2epsg.org/epsg/3082 P On Sat, Jan 8, 2011 at 8:18 PM, Aren Cambre wrote: > I have a SHP with this in its PRJ file: > > PROJCS["Custom",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",150.0],PARAMETER["False_Northing",500.0],PARAMETER["Central_Meridian",-100.0],PARAMETER["Standard_Parallel_1",27.5],PARAMETER["Standard_Parallel_2",35.0],PARAMETER["Central_Parallel",18.0],UNIT["Meter",1.0]] > > I need to get this into 3081 somehow since it's a good projection for Texas. > I have some 3081-reprojected PoistGIS data (originally 4269) for Texas > already. If I load this SHP into QGIS along with other 3081 data, it appears > well below the other data. QGIS indicates this SHP has a custom projection. > If I am not mistaken, to reproject with PostGIS, I need something besides -1 > in the geometry table. So I guess I need to specify some SRID with -s when I > run shp2pgsql? If so, what projection is this? > Thanks, and I apologize in advance for my naivety. > Aren > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to reproject data with custom projection?
I have a SHP with this in its PRJ file: * PROJCS["Custom",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",150.0],PARAMETER["False_Northing",500.0],PARAMETER["Central_Meridian",-100.0],PARAMETER["Standard_Parallel_1",27.5],PARAMETER["Standard_Parallel_2",35.0],PARAMETER["Central_Parallel",18.0],UNIT["Meter",1.0]] * I need to get this into 3081 somehow since it's a good projection for Texas. I have some 3081-reprojected PoistGIS data (originally 4269) for Texas already. If I load this SHP into QGIS along with other 3081 data, it appears well below the other data. QGIS indicates this SHP has a custom projection. If I am not mistaken, to reproject with PostGIS, I need something besides -1 in the geometry table. So I guess I need to specify some SRID with *-s* when I run shp2pgsql? If so, what projection is this? Thanks, and I apologize in advance for my naivety. Aren ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Missing primary key column. (SOLVED)
Ben has the solution. Indeed, the geometry column primary key was messed up. I deleted the old one, added the gid serial integer column and set it to the primary key. Works like a charm. On 1/8/2011 5:11 PM, Ben Madin wrote: Byron, A little bit more information may help here - what do you mean by 'importing' the table into QGIS? Have you checked that the primary key is unique - and such a constraint exists on the table definition? and that the column is in the geometry_columns table - you may want to try populate_geometry_columns() function. maybe post the output of \d tablename. cheers Ben On 09/01/2011, at 9:46 AM, Byron Como wrote: The problem: Missing primary key column in postgresql table imported into Quantum GIS. Details: 43,000 object database created using Windows XP 32 bit, postgresql 8.4, postgis 1.4, Quantum GIS 1.5. Daily backups using pgadmin. Several db restores were performed on the XP system for various reasons (one time was to recover from a lightning strike). Restored data was good and project continued with no problems. Recently upgraded to Windows 7 64 bit to be current and take advantage of the increased memory available. Installed the same software as above. The data in postgresql seems ok and can be manipulated normally - copied, pasted, deleted. Importing a table into Qgis 1.5 or 1.6 results in layer with the primary key column missing. The data can't be manipulated completely. The table can be added to but deletions can't be performed. The primary key is gid, integer. Corrective actions thus far: Reloaded software many times giving great attention to the details of installation. Examined the data in pgadmin for anomalies. Nothing apparent. All seems correct. Posted to Quantum GIS mailing list. Nothing there. Expected resolution: Barring solution from mailing list suggestions, reinstall on Windows XP 32 bit and continue with memory limitations as before. (Extra memory desired to make very large format high-resolution prints). ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] diagram of postgis geometry data type
Hello everybody I want to have a complete picture of postgis geometry data type. Could anyone give me a flow-chart, like the OGC geometry object model?? I know that postgis supports point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection and curved geometries. What about surfaces or TINs??? Thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Missing primary key column.
On 1/8/2011 5:11 PM, Ben Madin wrote: Byron, A little bit more information may help here - what do you mean by 'importing' the table into QGIS? Ach, "Add PostGIS Layer" was use. Have you checked that the primary key is unique Yes, it is. They are very simple tables. - and such a constraint exists on the table definition? It does, in each table. and that the column is in the geometry_columns table The gid column is missing from the geometry_columns table. AH HA! ah ha? Problem? - you may want to try populate_geometry_columns() function. As John Cleese said in "Silverado", "Today, my jurisdiction ends here." I'm clueless as how to proceed. Just add it, as the primary key? maybe post the output of \d tablename. cheers Ben On 09/01/2011, at 9:46 AM, Byron Como wrote: The problem: Missing primary key column in postgresql table imported into Quantum GIS. Details: 43,000 object database created using Windows XP 32 bit, postgresql 8.4, postgis 1.4, Quantum GIS 1.5. Daily backups using pgadmin. Several db restores were performed on the XP system for various reasons (one time was to recover from a lightning strike). Restored data was good and project continued with no problems. Recently upgraded to Windows 7 64 bit to be current and take advantage of the increased memory available. Installed the same software as above. The data in postgresql seems ok and can be manipulated normally - copied, pasted, deleted. Importing a table into Qgis 1.5 or 1.6 results in layer with the primary key column missing. The data can't be manipulated completely. The table can be added to but deletions can't be performed. The primary key is gid, integer. Corrective actions thus far: Reloaded software many times giving great attention to the details of installation. Examined the data in pgadmin for anomalies. Nothing apparent. All seems correct. Posted to Quantum GIS mailing list. Nothing there. Expected resolution: Barring solution from mailing list suggestions, reinstall on Windows XP 32 bit and continue with memory limitations as before. (Extra memory desired to make very large format high-resolution prints). ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Missing primary key column.
Byron, A little bit more information may help here - what do you mean by 'importing' the table into QGIS? Have you checked that the primary key is unique - and such a constraint exists on the table definition? and that the column is in the geometry_columns table - you may want to try populate_geometry_columns() function. maybe post the output of \d tablename. cheers Ben On 09/01/2011, at 9:46 AM, Byron Como wrote: > The problem: > Missing primary key column in postgresql table imported into Quantum GIS. > > Details: > 43,000 object database created using Windows XP 32 bit, postgresql 8.4, > postgis 1.4, Quantum GIS 1.5. > Daily backups using pgadmin. > Several db restores were performed on the XP system for various reasons (one > time was to recover from a lightning strike). Restored data was good and > project continued with no problems. > Recently upgraded to Windows 7 64 bit to be current and take advantage of the > increased memory available. > Installed the same software as above. > The data in postgresql seems ok and can be manipulated normally - copied, > pasted, deleted. > Importing a table into Qgis 1.5 or 1.6 results in layer with the primary key > column missing. The data can't be manipulated completely. The table can be > added to but deletions can't be performed. The primary key is gid, integer. > > Corrective actions thus far: > Reloaded software many times giving great attention to the details of > installation. Examined the data in pgadmin for anomalies. Nothing apparent. > All seems correct. Posted to Quantum GIS mailing list. Nothing there. > > Expected resolution: > Barring solution from mailing list suggestions, reinstall on Windows XP 32 > bit and continue with memory limitations as before. (Extra memory desired to > make very large format high-resolution prints). > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Missing primary key column.
The problem: Missing primary key column in postgresql table imported into Quantum GIS. Details: 43,000 object database created using Windows XP 32 bit, postgresql 8.4, postgis 1.4, Quantum GIS 1.5. Daily backups using pgadmin. Several db restores were performed on the XP system for various reasons (one time was to recover from a lightning strike). Restored data was good and project continued with no problems. Recently upgraded to Windows 7 64 bit to be current and take advantage of the increased memory available. Installed the same software as above. The data in postgresql seems ok and can be manipulated normally - copied, pasted, deleted. Importing a table into Qgis 1.5 or 1.6 results in layer with the primary key column missing. The data can't be manipulated completely. The table can be added to but deletions can't be performed. The primary key is gid, integer. Corrective actions thus far: Reloaded software many times giving great attention to the details of installation. Examined the data in pgadmin for anomalies. Nothing apparent. All seems correct. Posted to Quantum GIS mailing list. Nothing there. Expected resolution: Barring solution from mailing list suggestions, reinstall on Windows XP 32 bit and continue with memory limitations as before. (Extra memory desired to make very large format high-resolution prints). ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Desinscription du forum
Hello Please I want to Unsuscribe to PostGIS form because I have finished my instership. I also want to delele my messages. what can I do. Thanks Erik ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Computational Efficiency and Query Design Questions
Thank you Nicklas! Great answers. The latter query is useful for determining combinations of vehicle routes that would take a passenger from point a to point b, although there are yet other considerations, like distance and direction. Tom ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] MULTIPOINT WKT - parentheses or no parentheses?
Mike As far as i know, postgis accepts both formats as inputs. Specifically, if you want to represent a multipoint in syntax WKT, you will use comma to delimitate the values (e.g. "MULTIPOINT ( -1 1, 0 2, 3 4)" ). However, postgis use an alternate and acceptable WKT representation for multipoint using parenthesis to seperate each point (e.g "MULTIPOINT ((-1 1), (0 2), (3 4))" ), but will output the non-parenthetical version in the ST_AsText, ST_AsEWKT output. Now, i don't know if any application support both formats... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] MULTIPOINT WKT - parentheses or no parentheses?
Hello list, Hello all - does anyone know about whether the multipoint WKT format should have points separated with parentheses (e.g., "MULTIPOINT((1 2),(3 4))"), or should it not (e.g., "MULTIPOINT(1 2,3 4)")? PostGIS returns the latter (no parentheses), but if you look at the OGC doc linked from here here: http://www.opengeospatial.org/standards/sfa, the example for MULTIPOINT on page 61 has parentheses - same thing on Wikipedia: http://en.wikipedia.org/wiki/Well-known_text - I'm not sure what the correct OGC reference for WKB is though. I'm asking because the WKT parser in OpenLayers currently expects parentheses when its parser reads a multipoint, which is easy enough to work around. But should PostGIS be including parentheses if it is the accepted format (or is there a way to do this already?), or should we expect that any application that parses WKT must be able to pars both formats (e.g., like PostGIS does)? Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Computational Efficiency and Query Design Questions
Hallo Tom I will make a try answering as much as I can :-) On Sat, 2011-01-08 at 12:11 -0600, Tom Kessler wrote: > Hello to all, > > I'm developing some spatial queries and have these questions: > > 1) When one makes an Explain Select request, is there any > consideration given to the computational efficiencies > of the Postgis functions themselves composing the query?I doubt > it, but want to make sure. I assume selection and optimization > of the Postgis query functions is independent of the db query design. > Well, there actually is, but it is not fully used. Every function has a cost value that the planner uses to try to decide what the cheapest plan is. The problem is that the cost is constant for the function so the planner there is nothing telling "This is a huge polygon with hundred of thousands of vertexes, be aware" The planner only knows that a function with cost 100 is more expensive than a function with cost 1. Today there is no costs defined for almost any postgis function, as I understand it mostly because of the problem described above. The size and complexity of the geometry makes a bigger difference than the function. But it is possible to play with costs yourself. The below sets the cost to 1 (the default value for closestpoint) alter function st_closestpoint(geometry, geometry) cost 1; > 2) I have a polygon defined by a hundred points an external point, > and wish to calculate an approximate point on the polygon > closest to the external point. Assuming the error is acceptable, is > it more computationally efficient to calculate on the bounding box > than the polygon itself? > > a) papprox = ST_closestpoint( thegeom(polygon), p_ext)or > > b) papprox = ST_closestpoint(box2d(thegeom(polygon), p_ext)?? I guess your syntax is just to illustrate... The answer to your question depends on what geometries you are putting in. If they have more than 4 vertexes it should be faster when computing against the box. The box has only 2 corners, but it will be casted to a 4 vertex polygon before getting into the function. So, for a two vertex line it might be slower when using the box, but for a geometry with many vertex points it will be cheaper to use the bounding box. > > 3) I have a table of geometries and wish to make combinatorial > queries like: > > SELECT(a.thegeom, b.thegeom, c.thegeom, etc..WHERE a.thegeom and > b.thegeom touch > fixed points y and z and c.thegeom intersects geometries a and b. > Which is a better approach: > > a) build temporary tables for a and b geoms, then find c geoms that > intersect the entries in the temporary tablesor > > b) perform one select and interate through all the combinations. > The number of combinatorials goes exponential very quickly. If one > builds temporary > tables, how is the spatial index best transferred from original table > to the temporary ones ? Does one have to rebuild the index from > scratch. I don't think I understand exactly what you want to do, but comparing every possible combination, with the limitations you give in join statement and where statement is what a join does. The planner will try to find the best order to do things to get away as cheap as possible. Just take care of having working spatial indexes, then a very lot of combinations will be sorted away very fast. So, if I understand right what you want to do, it could look something like: SELECT a.thegeom, b.thegeom, c.thegeom FROM a.thegeom inner join b.thegeom on st_intersects(a.thegeom, b.thegeom) inner join c on st_intersects(a.thegeom, c.thegeom) and st_intersects(b.thegeom, c.thegeom) where st_intersects(a.thegeom, point_y) and st_intersects(a.thegeom, pointz) and st_intersects(b.thegeom, point_y) and st_intersects(b.thegeom, pointz) well, it looks very messy, but I think the planner will be able to do a good job here. But I can not figure out what it could be useful for :-) HTH Nicklas > thanks, > > Tom > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Computational Efficiency and Query Design Questions
Hello to all, I'm developing some spatial queries and have these questions: 1) When one makes an Explain Select request, is there any consideration given to the computational efficiencies of the Postgis functions themselves composing the query?I doubt it, but want to make sure. I assume selection and optimization of the Postgis query functions is independent of the db query design. 2) I have a polygon defined by a hundred points an external point, and wish to calculate an approximate point on the polygon closest to the external point. Assuming the error is acceptable, is it more computationally efficient to calculate on the bounding box than the polygon itself? a) papprox = ST_closestpoint( thegeom(polygon), p_ext)or b) papprox = ST_closestpoint(box2d(thegeom(polygon), p_ext)?? 3) I have a table of geometries and wish to make combinatorial queries like: SELECT(a.thegeom, b.thegeom, c.thegeom, etc..WHERE a.thegeom and b.thegeom touch fixed points y and z and c.thegeom intersects geometries a and b. Which is a better approach: a) build temporary tables for a and b geoms, then find c geoms that intersect the entries in the temporary tablesor b) perform one select and interate through all the combinations. The number of combinatorials goes exponential very quickly. If one builds temporary tables, how is the spatial index best transferred from original table to the temporary ones ? Does one have to rebuild the index from scratch. thanks, Tom ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
