[postgis-users] Geographies, geometries, international date line
Hi everyone. And so this is my first post to this mailing list, and since I registered, I haven't seen so many messages so I hope the level of this email is up to this list's standards. Here is the situation. At Expedia, we are working on upgrading our geo system and that implies migrating to PostGIS. During my tests, I did the following: I have two tables for regions, one, say region_geometry, keeps the polygon as a geometry(4326). The second one, say region_geography, keeps the polygon of a region as a Geography(4326). We also have other tables mapping hotels and points of interests, both storing their location as a geography point(4326). The test case is to find everything that is within a region. In other words, find all hotels and points of interests that are within a given region's polygon. The first test uses the region_geometry table, and the query uses ST_Within function (using geometries and the && operator). The second test uses the region_geography table, and the query uses ST_Intersects function (using geographies). I was expecting the geometry version to be much faster, but I actually find that performance is somewhat the same. Both queries use the special index, perform the index scan and so on. So how can this be possible, how can I be sure that geometries are actually faster than geographies ? The main issue with these two tests is regarding polygons crossing the international date line. So suppose I have a polygon that maps the Fiji islands. That polygon will cross the international date line. The geography method works fine, but the polygon, when used in geometry world, is actually a ribbon around the planet. I absolutely understand the reason, but if I really want to stay in the geometry world, how can I work around this problem ? Do I really have to split my polygon to create 2 polygons not crossing the line ? Do I have to filter my requests, find if the polygon crosses the date line and if so, "revert" to geographic calculations ? I have somewhat the same questions regarding distance calculations. In geometry world, if I want all the hotels in a 200 meters radius of a given point, I would use something like ST_Distance_spheroid(p1, p2) < 200. In geography, I would use ST_DWithin(p1, p2, 200). My tests shows that STD_Within (geography, geography) is most of the time faster than st_distance(p1, p2) with or without spheroid. Again, in both cases, the spatial indexes are used. I have many quite precise and edgy questions regarding good practices and performance tuning for PostGIS. I would like to provide samples and discuss around them, see what I / we can find. Is this list the right place to ask ? Maxime Poulin. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Area returning NaN near the poles?
Hi:
I'm working with some simple geography polygons that are near the poles. For
some of them (but not all), I'm getting NaN as the result of calling ST_Area.
(The odd numerical values for the polygons are the result of calling
ST_PixelAsPolygon() on tiles from a global-extent raster.)
This may be related to Trac #2006.
SELECT ST_Area(ST_GeogFromText('SRID=4326;POLYGON((-154.94561031
89.9,-154.91666122652 89.9,-154.91666122652
89.9166566643108,-154.94561031 89.9166566643108,-154.94561031
89.9))'));
returns NaN.
However, this polygon, just a bit to the east, returns a valid number:
SELECT ST_Area(ST_GeogFromText('SRID=4326;POLYGON((-146.61109933
89.9,-146.583327775422 89.9,-146.583327775422
89.9166566643108,-146.61109933 89.9166566643108,-146.61109933
89.9))'));
I'm running PostGIS 2.0.1 with PostgreSQL 9.1.4 on Mac OS/X 10.8.2:
mydatabase=# select PostGIS_Full_Version();
NOTICE: Function postgis_topology_scripts_installed() not found. Is topology
support enabled and topology.sql installed?
postgis_full_version
--
POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012"
GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.3" LIBJSON="UNKNOWN" RASTER
(1 row)
and have reproduced it on PostGIS 2.0.0 with PostgreSQL 9.1.4 on Ubuntu.
Best,
Pete
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
On Wed, Oct 10, 2012 at 12:16 PM, Sandro Santilli wrote: > > One benefit is making the spatial relationships explicit, so that > you could encode your counties as being composed by the districts, > and the districts would be composed by faces, which would be defined > by (shared) edges. > At that point querying for "all districts in a county" would just be > a matter of listing the components of a "county" TopoGeometry. > > But I guess you're still in the data preparation phase as you still > don't know which district record belong to which county, right ? > In that case you'll need ST_Covers (assuming the input data is > _really_ correct and doesn't contain cases in which the shape > of a district is slightly outside the shape of its county. > > Note that storing the data in a topology would be an excellent way to > have better control on those kind of data problems (often not easily > detectable as invisible to the eye). > > --strk; > +1 for this approach I have been using it for managing European administrative borders with excellent results! regards p -- Paolo Corti Geospatial software developer web: http://www.paolocorti.net twitter: @capooti skype: capooti ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial Reference ID
Thanks for the tip! On Wed, Oct 10, 2012 at 1:21 PM, Sandro Santilli wrote: > On Wed, Oct 10, 2012 at 01:07:14PM +0200, Ed Linde wrote: > > Thanks Sandro. > > Yes, that is what I want. So it seems that I don't have to do anything to > > the > > actual polygons. Like change them to be 2D and load them back into the DB > > then? > > Right, nothing to do. > In any case should you ever need to convert to 2D there's an ST_Force_2D > function (no need to do it outside the DB). > > --strk; > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > 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] Spatial Reference ID
On Wed, Oct 10, 2012 at 01:07:14PM +0200, Ed Linde wrote: > Thanks Sandro. > Yes, that is what I want. So it seems that I don't have to do anything to > the > actual polygons. Like change them to be 2D and load them back into the DB > then? Right, nothing to do. In any case should you ever need to convert to 2D there's an ST_Force_2D function (no need to do it outside the DB). --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial Reference ID
Thanks Sandro. Yes, that is what I want. So it seems that I don't have to do anything to the actual polygons. Like change them to be 2D and load them back into the DB then? Thanks, Ed On Wed, Oct 10, 2012 at 12:21 PM, Sandro Santilli wrote: > On Wed, Oct 10, 2012 at 12:13:53PM +0200, Ed Linde wrote: > > > The rows I get returned back, I am just wondering if its ok to intersect > 3D > > triangles (polygons) with a 2D box? Will this give me the correct > results? > > I just want to know all the triangles that are covered fully or partially > > by that 2D region. > > ST_Intersect only looks at 2D. > Only the "shadown" of your triangles will be considered. > > Seems to be what you want, right ? > > --strk; > ___ > 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] Spatial Reference ID
On Wed, Oct 10, 2012 at 12:13:53PM +0200, Ed Linde wrote: > The rows I get returned back, I am just wondering if its ok to intersect 3D > triangles (polygons) with a 2D box? Will this give me the correct results? > I just want to know all the triangles that are covered fully or partially > by that 2D region. ST_Intersect only looks at 2D. Only the "shadown" of your triangles will be considered. Seems to be what you want, right ? --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
Sandro I am definitely going to read up something on PostGIS Topology, as it sounds really interesting. Thanks for your advice! On 10/10/12, Sandro Santilli wrote: > On Wed, Oct 10, 2012 at 11:44:54AM +0200, Michal Zimmermann wrote: >> Thanks for the answers guys! I guess the data is ok, I was given them >> at the university which had bought it from the GIS company, that's why >> I trust this data source. Also when I try selecting the disctricts >> with ArcMap, it works (not with QGIS, though!). >> Sandro, would you be so kind and share some thoughts on PostGIS >> Topology? As I understood, it is a completely new feature of v2, >> right? What are the benefits? > > One benefit is making the spatial relationships explicit, so that > you could encode your counties as being composed by the districts, > and the districts would be composed by faces, which would be defined > by (shared) edges. > At that point querying for "all districts in a county" would just be > a matter of listing the components of a "county" TopoGeometry. > > But I guess you're still in the data preparation phase as you still > don't know which district record belong to which county, right ? > In that case you'll need ST_Covers (assuming the input data is > _really_ correct and doesn't contain cases in which the shape > of a district is slightly outside the shape of its county. > > Note that storing the data in a topology would be an excellent way to > have better control on those kind of data problems (often not easily > detectable as invisible to the eye). > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net > `-o--' > > >> On 10/10/12, Sandro Santilli wrote: >> > On Wed, Oct 10, 2012 at 10:27:42AM +0200, Michal Zimmermann wrote: >> >> Hi to all, >> >> I have a relation representing all the counties in my country and the >> >> other one containing all the districts - e. g. each and every county >> >> is composed of several districts. How do I select them with spatial >> >> query? I tried with >> >> select nazorp from kraje as k, orp_wgs as o WHERE >> >> ST_Within(o.geom,k.geom) >> >> AND >> >> k.nazev = 'Liberecký' // name of the county >> >> >> >> but had wrong results returned (well, they are probably not wrong, >> >> they are just not what I expected them to be). ST_Within only returns >> >> districts that don't share a boundary with the county, but I need to >> >> get all the districts within the county. Is that possible? I haven't >> >> found any built-in function suitable for my needs yet. >> > >> > Try ST_Covers, which includes the boundaries. >> > >> > PS: yours is the perfect use case for PostGIS Topology ! >> > >> > --strk; > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Michal Zimmermann (zimmi) WWW: http://www.zimmi.cz ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
On Wed, Oct 10, 2012 at 11:44:54AM +0200, Michal Zimmermann wrote: > Thanks for the answers guys! I guess the data is ok, I was given them > at the university which had bought it from the GIS company, that's why > I trust this data source. Also when I try selecting the disctricts > with ArcMap, it works (not with QGIS, though!). > Sandro, would you be so kind and share some thoughts on PostGIS > Topology? As I understood, it is a completely new feature of v2, > right? What are the benefits? One benefit is making the spatial relationships explicit, so that you could encode your counties as being composed by the districts, and the districts would be composed by faces, which would be defined by (shared) edges. At that point querying for "all districts in a county" would just be a matter of listing the components of a "county" TopoGeometry. But I guess you're still in the data preparation phase as you still don't know which district record belong to which county, right ? In that case you'll need ST_Covers (assuming the input data is _really_ correct and doesn't contain cases in which the shape of a district is slightly outside the shape of its county. Note that storing the data in a topology would be an excellent way to have better control on those kind of data problems (often not easily detectable as invisible to the eye). --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net `-o--' > On 10/10/12, Sandro Santilli wrote: > > On Wed, Oct 10, 2012 at 10:27:42AM +0200, Michal Zimmermann wrote: > >> Hi to all, > >> I have a relation representing all the counties in my country and the > >> other one containing all the districts - e. g. each and every county > >> is composed of several districts. How do I select them with spatial > >> query? I tried with > >> select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) > >> AND > >> k.nazev = 'Liberecký' // name of the county > >> > >> but had wrong results returned (well, they are probably not wrong, > >> they are just not what I expected them to be). ST_Within only returns > >> districts that don't share a boundary with the county, but I need to > >> get all the districts within the county. Is that possible? I haven't > >> found any built-in function suitable for my needs yet. > > > > Try ST_Covers, which includes the boundaries. > > > > PS: yours is the perfect use case for PostGIS Topology ! > > > > --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial Reference ID
Hi All, Thanks Mike. I have just loaded triangles as POLYGON with 3D (x,y,z) co-ordinates into postgres. And I want to know which triangles intersect with a 2D box or envelope. which I do something like the following: ST_INTERSECTS( a.geomtext, ST_MakeEnvelope( 585570 ,5205600, 585570, 5205630 )) 5 | POLYGON Z ((585570 5205570 634,585570 5205600 661,585549.994940603 5205588.81757012 560.308887892328,585570 5205570 634)) 6 | POLYGON Z ((585570 5205600 661,585600 5205570 644,585600 5205600 670,585570 5205600 661)) 7 | POLYGON Z ((585600 5205570 644,585570 5205600 661,585570 5205570 634,585600 5205570 644)) The rows I get returned back, I am just wondering if its ok to intersect 3D triangles (polygons) with a 2D box? Will this give me the correct results? I just want to know all the triangles that are covered fully or partially by that 2D region. Thanks, Ed On Wed, Oct 10, 2012 at 9:23 AM, Mike Toews wrote: > From the pastebin text: NAD27 UTM Zone 10N > http://spatialreference.org/ref/epsg/26710/ > SRID=26710 > Try it! > > -Mike > > On 10 October 2012 20:18, Ed Linde wrote: > > Hi All, > > I have this file I downloaded from geocomm and I am trying to figure out > > what the SRID for it in postgis would be. Have no idea. > > Can someone please help? > > Some sample x,y,z values in the file are > > > > 590283.357 5219156.537 658.84 > > 590283.357 5219146.537 656.303 > > 590283.357 5219136.537 660.872 > > 590283.357 5219126.537 666.917 > > 590283.357 5219116.537 671.999 > > > > > > The .txt file with spatial information --> http://pastebin.com/1H0zVYTQ > > > > I tried looking at http://spatialreference.org/. But could not figure > out > > which one I should be using in Postgis. > > I basically want to transform that co-ordinate system into something more > > manageable where say each > > x,y,z value lies in the range of [0,1000] or something like that. Would > be > > nice to hear some suggestions. > > > > Cheers, > > Ed > > > > ___ > > 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] Select polygons within polygon with PostGIS 2.0
Thanks for the answers guys! I guess the data is ok, I was given them at the university which had bought it from the GIS company, that's why I trust this data source. Also when I try selecting the disctricts with ArcMap, it works (not with QGIS, though!). Sandro, would you be so kind and share some thoughts on PostGIS Topology? As I understood, it is a completely new feature of v2, right? What are the benefits? On 10/10/12, Sandro Santilli wrote: > On Wed, Oct 10, 2012 at 10:27:42AM +0200, Michal Zimmermann wrote: >> Hi to all, >> I have a relation representing all the counties in my country and the >> other one containing all the districts - e. g. each and every county >> is composed of several districts. How do I select them with spatial >> query? I tried with >> select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) >> AND >> k.nazev = 'Liberecký' // name of the county >> >> but had wrong results returned (well, they are probably not wrong, >> they are just not what I expected them to be). ST_Within only returns >> districts that don't share a boundary with the county, but I need to >> get all the districts within the county. Is that possible? I haven't >> found any built-in function suitable for my needs yet. > > Try ST_Covers, which includes the boundaries. > > PS: yours is the perfect use case for PostGIS Topology ! > > --strk; > > http://www.cartodb.com - Map, analyze and build applications with your > data > >~~ http://strk.keybit.net > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Michal Zimmermann (zimmi) WWW: http://www.zimmi.cz ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
On Wed, Oct 10, 2012 at 10:27:42AM +0200, Michal Zimmermann wrote: > Hi to all, > I have a relation representing all the counties in my country and the > other one containing all the districts - e. g. each and every county > is composed of several districts. How do I select them with spatial > query? I tried with > select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) AND > k.nazev = 'Liberecký' // name of the county > > but had wrong results returned (well, they are probably not wrong, > they are just not what I expected them to be). ST_Within only returns > districts that don't share a boundary with the county, but I need to > get all the districts within the county. Is that possible? I haven't > found any built-in function suitable for my needs yet. Try ST_Covers, which includes the boundaries. PS: yours is the perfect use case for PostGIS Topology ! --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
Hello, I don't know how your differents layers has been made but your problem certainly rely on difference with boundaries even if there are very little different and just one point not into the county, your district will not be select. If you want to correct that you could use a buffer (st_within(o.geom,st_buffer(k.geom,XX)) big enough to correct the little differences of the boundaries but not too big although you may select too many districts. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Michal Zimmermann Date: mer. 10/10/2012 10:27 À: postgis-users@postgis.refractions.net Objet : [postgis-users] Select polygons within polygon with PostGIS 2.0 Hi to all, I have a relation representing all the counties in my country and the other one containing all the districts - e. g. each and every county is composed of several districts. How do I select them with spatial query? I tried with select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) AND k.nazev = 'Liberecký' // name of the county but had wrong results returned (well, they are probably not wrong, they are just not what I expected them to be). ST_Within only returns districts that don't share a boundary with the county, but I need to get all the districts within the county. Is that possible? I haven't found any built-in function suitable for my needs yet. -- Michal Zimmermann (zimmi) WWW: http://www.zimmi.cz ___ 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] Select polygons within polygon with PostGIS 2.0
Hi to all, I have a relation representing all the counties in my country and the other one containing all the districts - e. g. each and every county is composed of several districts. How do I select them with spatial query? I tried with select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) AND k.nazev = 'Liberecký' // name of the county but had wrong results returned (well, they are probably not wrong, they are just not what I expected them to be). ST_Within only returns districts that don't share a boundary with the county, but I need to get all the districts within the county. Is that possible? I haven't found any built-in function suitable for my needs yet. -- Michal Zimmermann (zimmi) WWW: http://www.zimmi.cz ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] co-ordinate extraction
couldnt find it ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial Reference ID
>From the pastebin text: NAD27 UTM Zone 10N http://spatialreference.org/ref/epsg/26710/ SRID=26710 Try it! -Mike On 10 October 2012 20:18, Ed Linde wrote: > Hi All, > I have this file I downloaded from geocomm and I am trying to figure out > what the SRID for it in postgis would be. Have no idea. > Can someone please help? > Some sample x,y,z values in the file are > > 590283.357 5219156.537 658.84 > 590283.357 5219146.537 656.303 > 590283.357 5219136.537 660.872 > 590283.357 5219126.537 666.917 > 590283.357 5219116.537 671.999 > > > The .txt file with spatial information --> http://pastebin.com/1H0zVYTQ > > I tried looking at http://spatialreference.org/. But could not figure out > which one I should be using in Postgis. > I basically want to transform that co-ordinate system into something more > manageable where say each > x,y,z value lies in the range of [0,1000] or something like that. Would be > nice to hear some suggestions. > > Cheers, > Ed > > ___ > 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] Spatial Reference ID
Hi All, I have this file I downloaded from geocomm and I am trying to figure out what the SRID for it in postgis would be. Have no idea. Can someone please help? Some sample x,y,z values in the file are 590283.357 5219156.537 658.84 590283.357 5219146.537 656.303 590283.357 5219136.537 660.872 590283.357 5219126.537 666.917 590283.357 5219116.537 671.999 The .txt file with spatial information --> http://pastebin.com/1H0zVYTQ I tried looking at http://spatialreference.org/. But could not figure out which one I should be using in Postgis. I basically want to transform that co-ordinate system into something more manageable where say each x,y,z value lies in the range of [0,1000] or something like that. Would be nice to hear some suggestions. Cheers, Ed ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
