[postgis-users] Trouble with returning MultiPolygon on psql query

[Birgit Laggner]

Hi Joseph,

I tried your intersects query after creating the multipolygon using the 
st_geomfromtext function with the coordinates from your geojson objects. 
There were no problems with the intersects query. The query returned a 
result even with the point within the lower polygon.

So my question would be: Are you sure, you did import all multipolygon 
parts in postgres? If you can't visualize the PostGIS geometries, 
perhaps you could dump the multipolygon in question to single polygons 
and compare the resulting number with the original as a first approach?

Regards,

Birgit


Am 12.09.2015 um 19:29 schrieb Joseph Spenner:
> I believe I may have overcomplicated my question.  My apologies.  I've 
> come up with a much simpler explanation of the issue:
>
> I have a MultiPolygon with 2 relatively simple polygons in it:
>
> http://microflush.org/json/MultiPolygon.json 
> <http://microflush.org/json/24.json>
>
>
> I've pulled out the 2 polygons from the above MultiPolygon below:
>
> http://microflush.org/json/upper.json
>
> http://microflush.org/json/lower.json
>
> Any/all of the 3 above can be copied/pasted into a GeoJSON tester to 
> view them:
> http://geojsonlint.com/
>
>
> I've stored the original MultiPolygon in PostgreSQL as described in my 
> original post:
>
> $ ogr2ogr -f "PostgreSQL" PG:"dbname=weatherzones user=postgres" 
> "MultiPolygon.json" -nln polys
>
> Here's how it looks in Postgres after the import:
>
> polytest=# \d polys;
>                                        Table "public.polys"
>     Column    |          Type | Modifiers
> --------------+-------------------------+---------------------------------------------------------
>  ogc_fid      | integer                 | not null default 
> nextval('polys_ogc_fid_seq'::regclass)
>  wkb_geometry | geometry(Geometry,4326) |
>  warnings     | character varying       |
> Indexes:
>     "polys_pk" PRIMARY KEY, btree (ogc_fid)
>     "polys_geom_idx" gist (wkb_geometry)
>
> polytest=#
>
>
> When I try to query postgres using single points which lie in the 
> lower polygon, I do not get rows returned.
> However, when I query using points which lie in the upper polygon, I 
> get the row returned.
>
> ie:
>
> This point lies in the lower polygon, and this query returns no rows:
>
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -116.024551 38.485773 )', 
> 4326), wkb_geometry);
>
>
> This next point lies in the upper polygon, and this query returns a 
> row, which is the MultiPolygon:
>
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -114.879913 39.249129 )', 
> 4326), wkb_geometry);
>
> Is there something wrong with my query, or perhaps the original 
> MultiPolygon which can explain why a query with a point from the lower 
> polygon won't return the MultiPolygon row?  I've done this with other 
> MultiPolygons and have not had an issue like this.
>
> Any help would be great.
>
> Thanks!
>
> Joseph Spenner
>
>
>
>
>
> ------------------------------------------------------------------------
> *From:* Joseph Spenner <joseph85750 at yahoo.com>
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Sent:* Wednesday, September 9, 2015 12:10 PM
> *Subject:* Trouble with returning MultiPolygon on psql query
>
> Hello, I have a file (source.json) containing 25 GeoJSON polygons with 
> weather alerts, which I imported into postgres using the following 
> command:
>
> $ ogr2ogr -f "PostgreSQL" PG:"dbname=weatherzones user=postgres" 
> "source.json" -nln polys
>
> Here's how it looks in Postgres:
>
> polytest=# \d polys;
> Table "public.polys"
>     Column    |          Type | Modifiers
> --------------+-------------------------+---------------------------------------------------------
>  ogc_fid      | integer | not null default 
> nextval('polys_ogc_fid_seq'::regclass)
>  wkb_geometry | geometry(Geometry,4326) |
>  warnings     | character varying |
> Indexes:
>     "polys_pk" PRIMARY KEY, btree (ogc_fid)
>     "polys_geom_idx" gist (wkb_geometry)
>
> polytest=#
>
> I can see it stored all of them:
>
> polytest=# select count(*) from polys;
>  count
> -------
>     25
> (1 row)
>
> polytest=#
>
> However, when I try to use a ST_Intersects(ST_PointFromText('POINT(), 
> I can't seem to get lines with MultiPolygons to return rows.  The 
> regular Polygons seem to work fine, though.
>
> For example, I have the following MultiPolygon in my table from Nevada:
> http://microflush.org/json/24.json
>
> Using a validator site, such as http://geojsonlint.com/ , I can 
> copy/paste that JSON into the interface and it looks like 2 adjacent 
> shapes making up the MultiPolygon.
>
> If I grab points from the upper section of the polygon, using 
> something like http://itouchmap.com/latlong.html, I get rows returned:
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -114.543457 39.336281 )', 
> 4326), wkb_geometry) limit 2;
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -115.658569 39.500297 )', 
> 4326), wkb_geometry) limit 2;
>
> But if I use a point from the lower Polygon section of that 
> MultiPolygon, I get zero:
>
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -115.977173 38.548667 )', 
> 4326), wkb_geometry) limit 2;
>
>
>
> Another example from Florida (a little more difficult to see, but same 
> issue):
> http://microflush.org/json/06.json
>
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -81.988228 27.034561 )', 4326), 
> wkb_geometry) limit 2;
>
> warnings
> ----------
> (0 rows)
>
> The above point (-81.988228 27.034561) would spacially exist in this 
> MultiPolygon.
>
>
> However, in this example, it will only return rows if I pick a point 
> inside the little polygon which shows up focused to the south of the 
> much larger polygon (when you use the geojsonlin.com site to plot the 
> whole thing).  For example, this point:
>
> polytest=# select warnings from polys where 
> ST_Intersects(ST_PointFromText('POINT( -81.9878 27.034 )', 4326), 
> wkb_geometry) limit 2;
> (1 row)
>
> So, it would seem the select statements which should return 
> Multipolygons are not returning for all Polygons contained in the 
> MultiPolygon.
>
> Is there something I'm doing incorrectly with my queries?
>
> Any help would be great.
>
> Thank you!
>
> Joseph Spenner
>
>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150917/9d2f83b5/attachment.html>