I find out what the bounding box is for a table that contains point geometry.
Newb alert: I have read about box2b, st_extent, st_envelope but none of those seem to be exactly what I need. Thanks in advance. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, August 12, 2010 2:00 PM To: [email protected] Subject: postgis-users Digest, Vol 100, Issue 10 Send postgis-users mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit http://postgis.refractions.net/mailman/listinfo/postgis-users or, via email, send a message with subject or body 'help' to [email protected] You can reach the person managing the list at [email protected] When replying, please edit your Subject line so it is more specific than "Re: Contents of postgis-users digest..." Today's Topics: 1. st_transform, irreversible? (Erik Rehn) 2. Re: st_transform, irreversible? (Paul Ramsey) 3. Re: st_transform, irreversible? (Paul Ramsey) 4. making polygons from center + vertex (Arnaud Sahuguet) 5. Re: making polygons from center + vertex (Francis Markham) 6. Re: making polygons from center + vertex (Arnaud Sahuguet) 7. Re: st_transform, irreversible? (Barend K?bben) 8. Re: st_transform, irreversible? (Mike Toews) 9. Re: st_transform, irreversible? (Ricardo Bayley) 10. Turn restrictions and directions of road on postgis pgrouting table. (FRANK RADA) 11. Detecting wrong geometries (Andrea Peri) 12. Re: Detecting wrong geometries (Nicolas Ribot) 13. Re: Detecting wrong geometries (Andrea Peri) 14. Convert already loaded table into a spatial table (points) (Appel, Tony) 15. Detecting wrong geometries (Andrea Peri) 16. Re: Convert already loaded table into a spatial table (points) (Fabio Renzo Panettieri) 17. Re: Convert already loaded table into a spatial table (points) (Fabio Renzo Panettieri) 18. Convert already loaded table into a spatial table (points) (Andrea Peri) 19. Re: Convert already loaded table into a spatial table (points) (Fred Lehodey) 20. Re: Convert already loaded table into a spatial table (points) (Paul Ramsey) ---------------------------------------------------------------------- Message: 1 Date: Wed, 11 Aug 2010 22:10:14 +0200 From: Erik Rehn <[email protected]> Subject: [postgis-users] st_transform, irreversible? To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hello Postgis Users! This is my first post on this list so I will start by asking a simple (and probably stupid) question. :) While using ST_AsKml() to produce an overlay for Google Earth I noticed that all my geometries where shifted slightly south-east. I figured this had something to do with the transformation between the projection that my geometries are stored in (SRID 3021) and WGS84 (4326) that is outputted by ST_AsKml() Just to test I ran this: SELECT ST_AsText( ST_Transform( ST_Transform( ST_GeomFromText('POINT(59 18)',4326), 3021), 4326)); I input a point in WGS84 (59,18), transforms it to 3021 and then back to WGS84. The result I get is: POINT(58.8672757036296 18.0394763349359) Can anyone explain this? Am I missing something regarding ST_Transform()? Im running Postgis 1.5 on Windows. Thank you for any help! /Erik -- Erik Rehn Slagkryssaren [email protected] www.slagkryssaren.com ------------------------------ Message: 2 Date: Wed, 11 Aug 2010 13:35:46 -0700 From: Paul Ramsey <[email protected]> Subject: Re: [postgis-users] st_transform, irreversible? To: [email protected], PostGIS Users Discussion <[email protected]> Cc: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 Seems to be an underlying problem with proj4: echo "59 21" | proj "+init=epsg:3021" | invproj "+init=epsg:3021" 58d49'47.733"E 21d2'54.745"N And that's without doing the datum shift part. P On Wed, Aug 11, 2010 at 1:10 PM, Erik Rehn <[email protected]> wrote: > Hello Postgis Users! > > This is my first post on this list so I will start by asking > a simple (and probably stupid) question. :) > > While using ST_AsKml() to produce an overlay for Google Earth I > noticed that all my geometries where shifted slightly south-east. > I figured this had something to do with the transformation between > the projection that my geometries are stored in (SRID 3021) and WGS84 (4326) > that is outputted by ST_AsKml() > > Just to test I ran this: > > SELECT ST_AsText( > ? ?ST_Transform( > ? ? ? ?ST_Transform( > ? ? ? ? ? ?ST_GeomFromText('POINT(59 18)',4326), > ? ? ? ?3021), > ? ?4326)); > > I input a point in WGS84 (59,18), transforms it to 3021 and then back to > WGS84. The result I get is: > POINT(58.8672757036296 18.0394763349359) > > Can anyone explain this? Am I missing something regarding ST_Transform()? > > Im running Postgis 1.5 on Windows. > > Thank you for any help! > /Erik > > -- > Erik Rehn > Slagkryssaren > [email protected] > www.slagkryssaren.com > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > ------------------------------ Message: 3 Date: Wed, 11 Aug 2010 13:39:48 -0700 From: Paul Ramsey <[email protected]> Subject: Re: [postgis-users] st_transform, irreversible? To: [email protected], PostGIS Users Discussion <[email protected]> Cc: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 Removing the EPSG lookup from the equation changes nothing: echo "59 21" | proj +proj=tmerc +lat_0=0 +lon_0=15.80827777777778 +k=1 +x_0=1500000 +y_0=0 +ellps=bessel +units=m +no_defs | invproj +proj=tmerc +lat_0=0 +lon_0=15.80827777777778 +k=1 +x_0=1500000 +y_0=0 +ellps=bessel +units=m +no_defs 58d49'47.733"E 21d2'54.745"N P. On Wed, Aug 11, 2010 at 1:35 PM, Paul Ramsey <[email protected]> wrote: > Seems to be an underlying problem with proj4: > > echo "59 21" | proj "+init=epsg:3021" | invproj "+init=epsg:3021" > > 58d49'47.733"E ?21d2'54.745"N > > And that's without doing the datum shift part. > > P > > On Wed, Aug 11, 2010 at 1:10 PM, Erik Rehn <[email protected]> wrote: >> Hello Postgis Users! >> >> This is my first post on this list so I will start by asking >> a simple (and probably stupid) question. :) >> >> While using ST_AsKml() to produce an overlay for Google Earth I >> noticed that all my geometries where shifted slightly south-east. >> I figured this had something to do with the transformation between >> the projection that my geometries are stored in (SRID 3021) and WGS84 (4326) >> that is outputted by ST_AsKml() >> >> Just to test I ran this: >> >> SELECT ST_AsText( >> ? ?ST_Transform( >> ? ? ? ?ST_Transform( >> ? ? ? ? ? ?ST_GeomFromText('POINT(59 18)',4326), >> ? ? ? ?3021), >> ? ?4326)); >> >> I input a point in WGS84 (59,18), transforms it to 3021 and then back to >> WGS84. The result I get is: >> POINT(58.8672757036296 18.0394763349359) >> >> Can anyone explain this? Am I missing something regarding ST_Transform()? >> >> Im running Postgis 1.5 on Windows. >> >> Thank you for any help! >> /Erik >> >> -- >> Erik Rehn >> Slagkryssaren >> [email protected] >> www.slagkryssaren.com >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > ------------------------------ Message: 4 Date: Wed, 11 Aug 2010 21:36:10 -0400 From: Arnaud Sahuguet <[email protected]> Subject: [postgis-users] making polygons from center + vertex To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" I found this really neat Google Maps example where you can draw polygons using 2 points: the center and one vertex. See http://www.barnabu.co.uk/geapi/polyplot/ Is there a POST GIS function to create the corresponding polygon? regards, -- Arnaud Sahuguet -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100811/04de817c/attachment-0001.html> ------------------------------ Message: 5 Date: Thu, 12 Aug 2010 12:04:03 +1000 From: Francis Markham <[email protected]> Subject: Re: [postgis-users] making polygons from center + vertex To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 Sure is, check out the ST_Buffer function http://postgis.refractions.net/docs/ST_Buffer.html ST_Buffer(geometry g1, float radius_of_buffer); If you give it a point as g1, and the radius of the circle it will calculate a pseduo-circular polygon for you. If you have two points g1 and g2 you could use ST_Buffer(g1, ST_Distance(g1, g2)); -Francis On 12 August 2010 11:36, Arnaud Sahuguet <[email protected]> wrote: > > I found this really neat Google Maps example where you can draw polygons > using 2 points: the center and one vertex. > See?http://www.barnabu.co.uk/geapi/polyplot/ > Is there a POST GIS function to create the corresponding polygon? > > regards, > > -- > Arnaud Sahuguet > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > ------------------------------ Message: 6 Date: Wed, 11 Aug 2010 22:30:10 -0400 From: Arnaud Sahuguet <[email protected]> Subject: Re: [postgis-users] making polygons from center + vertex To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" thanks. Arnaud On Wed, Aug 11, 2010 at 10:04 PM, Francis Markham <[email protected]>wrote: > Sure is, check out the ST_Buffer function > http://postgis.refractions.net/docs/ST_Buffer.html > ST_Buffer(geometry g1, float radius_of_buffer); > > If you give it a point as g1, and the radius of the circle it will > calculate a pseduo-circular polygon for you. If you have two points > g1 and g2 you could use ST_Buffer(g1, ST_Distance(g1, g2)); > > -Francis > > On 12 August 2010 11:36, Arnaud Sahuguet <[email protected]> > wrote: > > > > I found this really neat Google Maps example where you can draw polygons > using 2 points: the center and one vertex. > > See http://www.barnabu.co.uk/geapi/polyplot/ > > Is there a POST GIS function to create the corresponding polygon? > > > > regards, > > > > -- > > Arnaud Sahuguet > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Arnaud Sahuguet -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100811/6fd6f6ec/attachment-0001.html> ------------------------------ Message: 7 Date: Thu, 12 Aug 2010 16:24:17 +0200 From: Barend K?bben <[email protected]> Subject: Re: [postgis-users] st_transform, irreversible? To: PostGIS Users Discussion <[email protected]> Message-ID: <c889d0b1.141e0%[email protected]> Content-Type: text/plain; charset="iso-8859-1" I think that is because many projections can indeed not be reversed, i.e. the math is sometimes not straight formulas but iterative approximations and you'll get rounding errors, etcetera... -- Barend K?bben Senior Lecturer ITC ? University of Twente, Faculty of Geo-Information Science and Earth Observation PO Box 217, 7500AA Enschede, The Netherlands +31 (0)53 4874253 On 11-08-10 22:39, "Paul Ramsey" <[email protected]> wrote: > Removing the EPSG lookup from the equation changes nothing: > > echo "59 21" | proj +proj=tmerc +lat_0=0 +lon_0=15.80827777777778 +k=1 > +x_0=1500000 +y_0=0 +ellps=bessel +units=m +no_defs | invproj > +proj=tmerc +lat_0=0 +lon_0=15.80827777777778 +k=1 +x_0=1500000 +y_0=0 > +ellps=bessel +units=m +no_defs > > 58d49'47.733"E 21d2'54.745"N > > P. > > On Wed, Aug 11, 2010 at 1:35 PM, Paul Ramsey <[email protected]> wrote: >> Seems to be an underlying problem with proj4: >> >> echo "59 21" | proj "+init=epsg:3021" | invproj "+init=epsg:3021" >> >> 58d49'47.733"E ?21d2'54.745"N >> >> And that's without doing the datum shift part. >> >> P >> >> On Wed, Aug 11, 2010 at 1:10 PM, Erik Rehn <[email protected]> wrote: >>> Hello Postgis Users! >>> >>> This is my first post on this list so I will start by asking >>> a simple (and probably stupid) question. :) >>> >>> While using ST_AsKml() to produce an overlay for Google Earth I >>> noticed that all my geometries where shifted slightly south-east. >>> I figured this had something to do with the transformation between >>> the projection that my geometries are stored in (SRID 3021) and WGS84 (4326) >>> that is outputted by ST_AsKml() >>> >>> Just to test I ran this: >>> >>> SELECT ST_AsText( >>> ? ?ST_Transform( >>> ? ? ? ?ST_Transform( >>> ? ? ? ? ? ?ST_GeomFromText('POINT(59 18)',4326), >>> ? ? ? ?3021), >>> ? ?4326)); >>> >>> I input a point in WGS84 (59,18), transforms it to 3021 and then back to >>> WGS84. The result I get is: >>> POINT(58.8672757036296 18.0394763349359) >>> >>> Can anyone explain this? Am I missing something regarding ST_Transform()? >>> >>> Im running Postgis 1.5 on Windows. >>> >>> Thank you for any help! >>> /Erik >>> >>> -- >>> Erik Rehn >>> Slagkryssaren >>> [email protected] >>> www.slagkryssaren.com >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users Faculty of Geo-Information Science and Earth Observation (ITC) University of Twente E-mail disclaimer The information in this e-mail, including any attachments, is intended for the addressee only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the content of this information is strictly prohibited. If you have received this e-mail by mistake, please delete the message and any attachment and inform the sender by return e-mail. ITC accepts no liability for any error or omission in the message content or for damage of any kind that may arise as a result of e-mail transmission. ------------------------------ Message: 8 Date: Thu, 12 Aug 2010 07:39:36 -0700 From: Mike Toews <[email protected]> Subject: Re: [postgis-users] st_transform, irreversible? To: [email protected], PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 Hi, Your coordinates may be flipped. Was it 59N 18E? If so, use x,y notation: 'POINT(18 59)', which results in 'POINT(18.0000000000006 58.9999999999905)', which is close enough. Also keep in mind that you are outside the projection bounds: http://spatialreference.org/ref/epsg/3021/ (just a bit too far east). Whenever you are outside the projection bounds, the likelihood of storage precision errors increase. To understand why this is, you can think of taking the tangent of a two angles that are nearly a right-angle (89.9991 and 89.9992) which have very different results due to nature of the geometry. -Mike On 11 August 2010 13:10, Erik Rehn <[email protected]> wrote: > Hello Postgis Users! > > This is my first post on this list so I will start by asking > a simple (and probably stupid) question. :) > > While using ST_AsKml() to produce an overlay for Google Earth I > noticed that all my geometries where shifted slightly south-east. > I figured this had something to do with the transformation between > the projection that my geometries are stored in (SRID 3021) and WGS84 (4326) > that is outputted by ST_AsKml() > > Just to test I ran this: > > SELECT ST_AsText( > ? ?ST_Transform( > ? ? ? ?ST_Transform( > ? ? ? ? ? ?ST_GeomFromText('POINT(59 18)',4326), > ? ? ? ?3021), > ? ?4326)); > > I input a point in WGS84 (59,18), transforms it to 3021 and then back to > WGS84. The result I get is: > POINT(58.8672757036296 18.0394763349359) > > Can anyone explain this? Am I missing something regarding ST_Transform()? > > Im running Postgis 1.5 on Windows. > > Thank you for any help! > /Erik > > -- > Erik Rehn > Slagkryssaren > [email protected] > www.slagkryssaren.com > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > ------------------------------ Message: 9 Date: Thu, 12 Aug 2010 12:05:03 -0300 From: Ricardo Bayley <[email protected]> Subject: Re: [postgis-users] st_transform, irreversible? To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="utf-8" very nice explanation Mike On Thu, Aug 12, 2010 at 11:39 AM, Mike Toews <[email protected]> wrote: > Hi, > > Your coordinates may be flipped. Was it 59N 18E? If so, use x,y > notation: 'POINT(18 59)', which results in 'POINT(18.0000000000006 > 58.9999999999905)', which is close enough. > > Also keep in mind that you are outside the projection bounds: > http://spatialreference.org/ref/epsg/3021/ (just a bit too far east). > Whenever you are outside the projection bounds, the likelihood of > storage precision errors increase. To understand why this is, you can > think of taking the tangent of a two angles that are nearly a > right-angle (89.9991 and 89.9992) which have very different results > due to nature of the geometry. > > -Mike > > On 11 August 2010 13:10, Erik Rehn <[email protected]> wrote: > > Hello Postgis Users! > > > > This is my first post on this list so I will start by asking > > a simple (and probably stupid) question. :) > > > > While using ST_AsKml() to produce an overlay for Google Earth I > > noticed that all my geometries where shifted slightly south-east. > > I figured this had something to do with the transformation between > > the projection that my geometries are stored in (SRID 3021) and WGS84 > (4326) > > that is outputted by ST_AsKml() > > > > Just to test I ran this: > > > > SELECT ST_AsText( > > ST_Transform( > > ST_Transform( > > ST_GeomFromText('POINT(59 18)',4326), > > 3021), > > 4326)); > > > > I input a point in WGS84 (59,18), transforms it to 3021 and then back to > > WGS84. The result I get is: > > POINT(58.8672757036296 18.0394763349359) > > > > Can anyone explain this? Am I missing something regarding ST_Transform()? > > > > Im running Postgis 1.5 on Windows. > > > > Thank you for any help! > > /Erik > > > > -- > > Erik Rehn > > Slagkryssaren > > [email protected] > > www.slagkryssaren.com > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/db589486/attachment-0001.html> ------------------------------ Message: 10 Date: Thu, 12 Aug 2010 10:19:43 -0500 From: FRANK RADA <[email protected]> Subject: [postgis-users] Turn restrictions and directions of road on postgis pgrouting table. To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 ---------- Forwarded message ---------- From: FRANK RADA <[email protected]> Date: 2010/8/6 Subject: Turn restrictions and directions of road on postgis pgrouting table. To: [email protected] HI. firts that all , excuse my english, it's very bad. i tell to you that I'm doing a routing program, i build the graph table with the pgrouting functions, this is: the tmp_vertices table and the ways with edges table. but i need model the turn restrictions and ?directions of road. any idea? appreciate your help. ------------------------------ Message: 11 Date: Thu, 12 Aug 2010 18:51:17 +0200 From: Andrea Peri <[email protected]> Subject: [postgis-users] Detecting wrong geometries To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" Hi, I needed to test a shapefile to find eventually wrong geometries. My first idea was to use postgis to do a select *from table_from_shapefile where ST_IsValid(geom)=false; So I load the shapefile with shp2pgsql in postgis (1.5.1) and do this query. The results was 1 only geometry wrong. After some days I repeat the same test with Grass. Grass with my big surprise report me more geometry wrong. Infact in the shapefile was detected many geometries with wrong orientation. Now my question is why this was not detected from postgis ? I don't know why, but my idea is that perhaps shp2pgsql, when find a geometry wrong oriented correct it automatically . Is this correct ? Thx, Andrea Peri. -- ----------------- Andrea Peri . . . . . . . . . qwerty ????? ----------------- -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/d178cef5/attachment-0001.html> ------------------------------ Message: 12 Date: Thu, 12 Aug 2010 19:14:48 +0200 From: Nicolas Ribot <[email protected]> Subject: Re: [postgis-users] Detecting wrong geometries To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset=ISO-8859-1 On 12 August 2010 18:51, Andrea Peri <[email protected]> wrote: > Hi, > > I needed to test a shapefile to find eventually wrong geometries. > > My first idea was to use postgis to do a > select *from table_from_shapefile where ST_IsValid(geom)=false; > > So I load the shapefile with shp2pgsql in postgis (1.5.1) > and do this query. > The results was 1 only geometry wrong. > > After some days I repeat the same test with Grass. > > Grass with my big surprise report me more geometry wrong. > Infact in the shapefile was detected many geometries with wrong orientation. > > Now my question is why this was not detected from postgis ? > I don't know why, > but my idea is that perhaps shp2pgsql, when find a geometry wrong oriented > correct it automatically . > > Is this correct ? > > Thx, > Hi, Polygon orientation is not important with respect to OGC specifications and does not define an invalid polygon. That's why st_isvalid() returns true in Postgis. You can, however, force polygon orientation using the st_forceRHR() function (http://postgis.refractions.net/docs/ST_ForceRHR.html). Nicolas PS. the Jump software (http://www.vividsolutions.com/jump/) has a nice validation tool in which you can enable this constraint when validating a layer. With the postgis plugin, you can perform this validation directly on a postgis table. It graphically spots invalid geometries and also generate a new layer, with attributes telling the reason of invalidity. ------------------------------ Message: 13 Date: Thu, 12 Aug 2010 19:41:27 +0200 From: Andrea Peri <[email protected]> Subject: Re: [postgis-users] Detecting wrong geometries To: Brent Wood <[email protected]> Cc: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" 2010/8/12 Brent Wood <[email protected]> > Hi Andrea, > > What do you mean by a "wrong" geometry? One that failed to load? > > Did you use the postgis function ST_isvalid() function to test? > > Brent Wood > Yes, After load in postgres I try a select count(*) from table where ST_IsValid(geom)=false; and have a result of 1 only invalid geometry. -- ----------------- Andrea Peri . . . . . . . . . qwerty ????? ----------------- -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/6d526223/attachment-0001.html> ------------------------------ Message: 14 Date: Thu, 12 Aug 2010 12:53:54 -0500 From: "Appel, Tony" <[email protected]> Subject: [postgis-users] Convert already loaded table into a spatial table (points) To: "[email protected]" <[email protected]> Message-ID: <64e02d5c219fd949b70c6d871b37406b0cb51c4...@f-exch-mb1.ad.navteq.com> Content-Type: text/plain; charset="us-ascii" I have already loaded a table that contains X, Y and Z values. AT the time of loading it was thought we did not need a spatial component to this table. Now we realize that we do have a need but do not want to reload the 190 million records again and create the point features on load. Is it possible to create the point geometry in the existing table....if so, how? Thanks in advance. Tony Appel The information contained in this communication may be CONFIDENTIAL and is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please notify the sender and delete/destroy the original message and any copy of it from your computer or paper files. -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/c286b87f/attachment-0001.html> ------------------------------ Message: 15 Date: Thu, 12 Aug 2010 20:02:07 +0200 From: Andrea Peri <[email protected]> Subject: [postgis-users] Detecting wrong geometries To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" >Hi, > >Polygon orientation is not important with respect to OGC >specifications and does not define an invalid polygon. >That's why st_isvalid() returns true in Postgis. >You can, however, force polygon orientation using the st_forceRHR() >function (http://postgis.refractions.net/docs/ST_ForceRHR.html). > >Nicolas Thx for clear response. But I like to understand better why this strategy. When load from a shapefile with shp2pgsql the source of data is a shapefile with follow the ESRI specifics, and it is not an OGC source. So the meaning of the geometry in the shapefile must following the specific of the esri document. This mean that a geometry counter-clockwise is a hole, or is a mistake. If it is a hole then if the goal is to load exactly what there is in the shapefile is needed to load the hole :). If it is a mistake then is needed report the mistake and not load that geometry. I prefer the first choice of course, but I'm not sure to understand why load something else that is changed from the original is better. -- ----------------- Andrea Peri . . . . . . . . . qwerty ????? ----------------- -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/cfd78919/attachment-0001.html> ------------------------------ Message: 16 Date: Thu, 12 Aug 2010 15:03:46 -0300 From: Fabio Renzo Panettieri <[email protected]> Subject: Re: [postgis-users] Convert already loaded table into a spatial table (points) To: PostGIS Users Discussion <[email protected]> Message-ID: <1281636226.5699.3.ca...@radon> Content-Type: text/plain; charset="UTF-8" On Thu, 2010-08-12 at 12:53 -0500, Appel, Tony wrote: > I have already loaded a table that contains X, Y and Z values. AT the > time of loading it was thought we did not need a spatial component to > this table. > > > > Now we realize that we do have a need but do not want to reload the > 190 million records again and create the point features on load. > > > > Is it possible to create the point geometry in the existing table?.if > so, how? You can always add a new geometry column to your existing table. SELECT AddGeometryColumn ('public','my_table','the_geom',4326,'POINT',2); http://postgis.refractions.net/docs/AddGeometryColumn.html And after that you create the geometries, assuming you have points stored: UPDATE my_table SET the_geom = ST_MakePoint(x, y, z); http://postgis.refractions.net/docs/ST_MakePoint.html -- Fabio R. Panettieri Software Architect http://www.xoomcode.com ------------------------------ Message: 17 Date: Thu, 12 Aug 2010 15:06:54 -0300 From: Fabio Renzo Panettieri <[email protected]> Subject: Re: [postgis-users] Convert already loaded table into a spatial table (points) To: [email protected], PostGIS Users Discussion <[email protected]> Message-ID: <1281636414.5699.5.ca...@radon> Content-Type: text/plain; charset="UTF-8" > SELECT AddGeometryColumn > ('public','my_table','the_geom',4326,'POINT',2); > http://postgis.refractions.net/docs/AddGeometryColumn.html Ups, my mistake. This should be SELECT AddGeometryColumn ('public','my_table','the_geom',4326,'POINT',3); -- Fabio R. Panettieri Software Architect http://www.xoomcode.com ------------------------------ Message: 18 Date: Thu, 12 Aug 2010 20:09:32 +0200 From: Andrea Peri <[email protected]> Subject: [postgis-users] Convert already loaded table into a spatial table (points) To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" >I have already loaded a table that contains X, Y and Z values. AT the time of >loading it was thought we did not need a spatial component to this table. >Now we realize that we do have a need but do not want to reload the 190 >million records again and create the point features on load. >Is it possible to create the point geometry in the existing table....if so, >how? >Thanks in advance. >Tony Appel Assume the X,Y,Z are in three fields in a "tablename" table. SELECT AddGeometryColumn( 'public','tablename','geom', <epsg>, 'POINT', 3 ); update public.tablename set geom = ST_MakePoint(X::double,Y::double,Z::double); epsg is a integer value of your SRS (epsg code) Regards, -- ----------------- Andrea Peri . . . . . . . . . qwerty ????? ----------------- -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/796fe128/attachment-0001.html> ------------------------------ Message: 19 Date: Thu, 12 Aug 2010 19:14:40 +0100 From: Fred Lehodey <[email protected]> Subject: Re: [postgis-users] Convert already loaded table into a spatial table (points) To: PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset="iso-8859-1" Hi, ST_MakePoint return point with unknow geometry. You need to use something like: ST_SetSRID(ST_MakePoint(x,y,z),<your_srid>) Fred On Thu, Aug 12, 2010 at 7:09 PM, Andrea Peri <[email protected]> wrote: > > >I have already loaded a table that contains X, Y and Z values. AT the time > >of loading it was thought we did not need a spatial component to this table. > >Now we realize that we do have a need but do not want to reload the 190 > >million records again and create the point features on load. > > >Is it possible to create the point geometry in the existing table....if so, > >how? > >Thanks in advance. > >Tony Appel > > > Assume the X,Y,Z are in three fields in a "tablename" table. > > SELECT AddGeometryColumn( 'public','tablename','geom', <epsg>, 'POINT', 3 ); > > update public.tablename set geom = > ST_MakePoint(X::double,Y::double,Z::double); > > epsg is a integer value of your SRS (epsg code) > > > Regards, > > -- > ----------------- > Andrea Peri > . . . . . . . . . > qwerty ????? > ----------------- > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20100812/05c8e824/attachment-0001.html> ------------------------------ Message: 20 Date: Thu, 12 Aug 2010 11:56:57 -0700 From: Paul Ramsey <[email protected]> Subject: Re: [postgis-users] Convert already loaded table into a spatial table (points) To: [email protected], PostGIS Users Discussion <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain; charset=windows-1252 Because of transaction handling, updating every row in a database is the equivalent of a full load. It might actually take longer than the initial load. I'd suggest something like create newtable as select st_makepoint(x,y) as point, a1, a2, a2... from oldtable drop oldtable alter newtable rename to oldtable P. On Thu, Aug 12, 2010 at 11:03 AM, Fabio Renzo Panettieri <[email protected]> wrote: > On Thu, 2010-08-12 at 12:53 -0500, Appel, Tony wrote: >> I have already loaded a table that contains X, Y and Z values. AT the >> time of loading it was thought we did not need a spatial component to >> this table. >> >> >> >> Now we realize that we do have a need but do not want to reload the >> 190 million records again and create the point features on load. >> >> >> >> Is it possible to create the point geometry in the existing table?.if >> so, how? > > > You can always add a new geometry column to your existing table. > > SELECT AddGeometryColumn > ('public','my_table','the_geom',4326,'POINT',2); > http://postgis.refractions.net/docs/AddGeometryColumn.html > > And after that you create the geometries, assuming you have points stored: > > UPDATE my_table SET the_geom = ST_MakePoint(x, y, z); > http://postgis.refractions.net/docs/ST_MakePoint.html > > > -- > Fabio R. Panettieri > Software Architect > http://www.xoomcode.com > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > ------------------------------ _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users End of postgis-users Digest, Vol 100, Issue 10 ********************************************** The information contained in this communication may be CONFIDENTIAL and is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please notify the sender and delete/destroy the original message and any copy of it from your computer or paper files. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
