[postgis-users] Calculate Bounding Box of a point type table
Appel, Tony
tony.appel at navteq.com
Wed Aug 18 08:24:31 PDT 2010
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: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of postgis-users-request at postgis.refractions.net
Sent: Thursday, August 12, 2010 2:00 PM
To: postgis-users at postgis.refractions.net
Subject: postgis-users Digest, Vol 100, Issue 10
Send postgis-users mailing list submissions to
postgis-users at postgis.refractions.net
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
postgis-users-request at postgis.refractions.net
You can reach the person managing the list at
postgis-users-owner at postgis.refractions.net
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 <erik at slagkryssaren.com>
Subject: [postgis-users] st_transform, irreversible?
To: postgis-users at postgis.refractions.net
Message-ID: <4C6303A6.3010108 at slagkryssaren.com>
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
erik at slagkryssaren.com
www.slagkryssaren.com
------------------------------
Message: 2
Date: Wed, 11 Aug 2010 13:35:46 -0700
From: Paul Ramsey <pramsey at opengeo.org>
Subject: Re: [postgis-users] st_transform, irreversible?
To: erik at slagkryssaren.com, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Cc: metacrs at lists.osgeo.org
Message-ID:
<AANLkTin4TvWR=ccBxSHM4WW3o1nt23B2nf49sJtAUGPg at mail.gmail.com>
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 <erik at slagkryssaren.com> 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
> erik at slagkryssaren.com
> www.slagkryssaren.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 3
Date: Wed, 11 Aug 2010 13:39:48 -0700
From: Paul Ramsey <pramsey at opengeo.org>
Subject: Re: [postgis-users] st_transform, irreversible?
To: erik at slagkryssaren.com, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Cc: metacrs at lists.osgeo.org
Message-ID:
<AANLkTim_JWEi9xTE6tR90kLqcU3pB7wEx6h2NsT9WyKn at mail.gmail.com>
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 <pramsey at opengeo.org> 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 <erik at slagkryssaren.com> 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
>> erik at slagkryssaren.com
>> www.slagkryssaren.com
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
------------------------------
Message: 4
Date: Wed, 11 Aug 2010 21:36:10 -0400
From: Arnaud Sahuguet <arnaud.sahuguet at gmail.com>
Subject: [postgis-users] making polygons from center + vertex
To: postgis-users at postgis.refractions.net
Message-ID:
<AANLkTi=CO=7fxTqbFcMgXwpkcfvKByQ3uk93q18_=PvB at mail.gmail.com>
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 <fmarkham at gmail.com>
Subject: Re: [postgis-users] making polygons from center + vertex
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTimRXnk-ySt8GcHa_Rc6ZuAZ0yy2agp_QCsDCbQp at mail.gmail.com>
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 <arnaud.sahuguet at gmail.com> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 6
Date: Wed, 11 Aug 2010 22:30:10 -0400
From: Arnaud Sahuguet <arnaud.sahuguet at gmail.com>
Subject: Re: [postgis-users] making polygons from center + vertex
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTikc_DXC4u-oqBNgci-9Gv7V4Y8wMsPXU+Xo0hQ7 at mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
thanks.
Arnaud
On Wed, Aug 11, 2010 at 10:04 PM, Francis Markham <fmarkham at gmail.com>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 <arnaud.sahuguet at gmail.com>
> 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
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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 <kobben at itc.nl>
Subject: Re: [postgis-users] st_transform, irreversible?
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <C889D0B1.141E0%kobben at itc.nl>
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" <pramsey at opengeo.org> 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 <pramsey at opengeo.org> 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 <erik at slagkryssaren.com> 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
>>> erik at slagkryssaren.com
>>> www.slagkryssaren.com
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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 <mwtoews at gmail.com>
Subject: Re: [postgis-users] st_transform, irreversible?
To: erik at slagkryssaren.com, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTikb76+yzZEMfP7gqc5W55qEGEpyttuhUk5hcMPG at mail.gmail.com>
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 <erik at slagkryssaren.com> 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
> erik at slagkryssaren.com
> www.slagkryssaren.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 9
Date: Thu, 12 Aug 2010 12:05:03 -0300
From: Ricardo Bayley <ricardo.bayley at gmail.com>
Subject: Re: [postgis-users] st_transform, irreversible?
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTimv2j2m1N1f7y3nr3GKvQUxzB1X-L0oByKFznuM at mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
very nice explanation Mike
On Thu, Aug 12, 2010 at 11:39 AM, Mike Toews <mwtoews at gmail.com> 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 <erik at slagkryssaren.com> 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
> > erik at slagkryssaren.com
> > www.slagkryssaren.com
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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 <frankrada at gmail.com>
Subject: [postgis-users] Turn restrictions and directions of road on
postgis pgrouting table.
To: postgis-users at postgis.refractions.net
Message-ID:
<AANLkTi=AmPB2SQ4diHRHCCf6neXno33g3JEFf5+hgk10 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
---------- Forwarded message ----------
From: FRANK RADA <frankrada at gmail.com>
Date: 2010/8/6
Subject: Turn restrictions and directions of road on postgis pgrouting table.
To: postgis-users at postgis.refractions.net
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 <aperi2007 at gmail.com>
Subject: [postgis-users] Detecting wrong geometries
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTi=AfO6OL2MDpCoSDg3tQ4aM2BmR33KDo6Wkik9B at mail.gmail.com>
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 <nicolas.ribot at gmail.com>
Subject: Re: [postgis-users] Detecting wrong geometries
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTimr6dFvzuknEBjQx=+xpZYp4MMu-OH_FNNoG1Rx at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
On 12 August 2010 18:51, Andrea Peri <aperi2007 at gmail.com> 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 <aperi2007 at gmail.com>
Subject: Re: [postgis-users] Detecting wrong geometries
To: Brent Wood <pcreso at yahoo.com>
Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTikd86xECfdpsvHScQyzTW9ua8beb0_qTcMG3RNS at mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
2010/8/12 Brent Wood <pcreso at yahoo.com>
> 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" <tony.appel at navteq.com>
Subject: [postgis-users] Convert already loaded table into a spatial
table (points)
To: "postgis-users at postgis.refractions.net"
<postgis-users at postgis.refractions.net>
Message-ID:
<64E02D5C219FD949B70C6D871B37406B0CB51C4C67 at 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 <aperi2007 at gmail.com>
Subject: [postgis-users] Detecting wrong geometries
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTin4ne0iM=fUoG_CsBx0HYwQON5PLdw+DJn3zjPC at mail.gmail.com>
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 <fpanettieri at xoomcode.com>
Subject: Re: [postgis-users] Convert already loaded table into a
spatial table (points)
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <1281636226.5699.3.camel at 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 <fpanettieri at xoomcode.com>
Subject: Re: [postgis-users] Convert already loaded table into a
spatial table (points)
To: fpanettieri at xoomcode.com, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Message-ID: <1281636414.5699.5.camel at 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 <aperi2007 at gmail.com>
Subject: [postgis-users] Convert already loaded table into a spatial
table (points)
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTimeZ6Q+nUotaPi873SERSLc+HLSaxuP06yH1rQm at mail.gmail.com>
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 <lehodey at gmail.com>
Subject: Re: [postgis-users] Convert already loaded table into a
spatial table (points)
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTim-K6bU3ZebbBxtS+1DAccNe67ksSJC1f0KRj9q at mail.gmail.com>
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 <aperi2007 at gmail.com> 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
> postgis-users at postgis.refractions.net
> 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 <pramsey at opengeo.org>
Subject: Re: [postgis-users] Convert already loaded table into a
spatial table (points)
To: fpanettieri at xoomcode.com, PostGIS Users Discussion
<postgis-users at postgis.refractions.net>
Message-ID:
<AANLkTi=QaybGCg7srVYaRA7042PQ4D+kn6_ZMkubbMsv at mail.gmail.com>
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
<fpanettieri at xoomcode.com> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
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.
More information about the postgis-users
mailing list