Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Hello,
Maybe I don’t understand your problem very well but I think your query for
points should work with a polygon using ST_GeomFromText
(http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon or a
subquery if it stored into your DB.
Hugues.
De : postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : lundi 17 novembre 2014 22:35
À : PostGIS Users Discussion
Objet : [postgis-users] Returning polygons if any of a polygon input points lie
in the polygons
Hello, I'm trying to perform a query which will return any polygons which have
any points in common with an input polygon.
So, if I have Polygon A.
In my database, I have several other polygons stored.
I want to submit a query with Polygon A as the input, which will return all of
the polygons in my database which have any points in common with Polygon A.
Can this be done?
I found this:
http://postgis.net/docs/ST_Overlaps.html
But from what I can tell, it only returns a T or F. I need to fetch the
actual polygons if T.
I'm currently able to query with points as input:
select zone,gid,state,name,ST_Asgeojson( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
But now I have a need to use polygons as input.
Any help would be great.
Thanks!
Regards,
Joseph Spenner
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Something like this?
SELECT zone,gid,state,name,ST_Asgeojson( geom )
FROM polys
WHERE ST_Overlaps(ST_GeomFromText('POLYGON((-109 42,-108 42, -108 43; -109
43, -109 42))', 4269), geom)=TRUE;
Cheers,
Rémi-C
2014-11-18 9:00 GMT+01:00 Hugues François hugues.franc...@irstea.fr:
Hello,
Maybe I don’t understand your problem very well but I think your query for
points should work with a polygon using ST_GeomFromText (
http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon
or a subquery if it stored into your DB.
Hugues.
*De :* postgis-users-boun...@lists.osgeo.org [mailto:
postgis-users-boun...@lists.osgeo.org] *De la part de* Joseph Spenner
*Envoyé :* lundi 17 novembre 2014 22:35
*À :* PostGIS Users Discussion
*Objet :* [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Hello, I'm trying to perform a query which will return any polygons which
have any points in common with an input polygon.
So, if I have Polygon A.
In my database, I have several other polygons stored.
I want to submit a query with Polygon A as the input, which will return
all of the polygons in my database which have any points in common with
Polygon A.
Can this be done?
I found this:
http://postgis.net/docs/ST_Overlaps.html
But from what I can tell, it only returns a T or F. I need to fetch the
actual polygons if T.
I'm currently able to query with points as input:
select zone,gid,state,name,ST_Asgeojson( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
But now I have a need to use polygons as input.
Any help would be great.
Thanks!
Regards,
Joseph Spenner
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Rémi: Yes, that worked perfectly. Thanks! My input poly will be stored in
another database (maybe just another table). It would be nice to perform the
query all on 1 line (get the poly and use it for the query), but I can query
the first table first to get the input poly, then use that for the 2nd query
without too much difficulty.
Thanks again for the responses!
Regards,Joseph Spenner
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Rémi Cura remi.c...@gmail.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Cc: Joseph Spenner joseph85...@yahoo.com
Sent: Tuesday, November 18, 2014 4:20 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Something like this?
SELECT zone,gid,state,name,ST_Asgeojson( geom )
FROM polys
WHERE ST_Overlaps(ST_GeomFromText('POLYGON((-109 42,-108 42, -108 43; -109 43,
-109 42))', 4269), geom)=TRUE;
Cheers,
Rémi-C
2014-11-18 9:00 GMT+01:00 Hugues François hugues.franc...@irstea.fr:
Hello, Maybe I don’t understand your problem very well but I think your query
for points should work with a polygon using ST_GeomFromText
(http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon or a
subquery if it stored into your DB. Hugues. De :
postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : lundi 17 novembre 2014 22:35
À : PostGIS Users Discussion
Objet : [postgis-users] Returning polygons if any of a polygon input points lie
in the polygons Hello, I'm trying to perform a query which will return any
polygons which have any points in common with an input polygon. So, if I have
Polygon A.In my database, I have several other polygons stored. I want to
submit a query with Polygon A as the input, which will return all of the
polygons in my database which have any points in common with Polygon A. Can
this be done?
I found this:http://postgis.net/docs/ST_Overlaps.html But from what I can tell,
it only returns a T or F. I need to fetch the actual polygons if T. I'm
currently able to query with points as input: select
zone,gid,state,name,ST_Asgeojson( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
But now I have a need to use polygons as input.
Any help would be great.
Thanks! Regards,Joseph Spenner
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Something I did notice though-- it seems that if the input polygon completely
surrounds a polygon stored in my database, it is not included in the result. I
read something about this here: http://postgis.net/docs/ST_Overlaps.html
Returns TRUE if the Geometries spatially overlap. By that we mean they
intersect, but one does not completely contain another.
I do need the polygons which are completely contained as well. Can this be
done?
Thanks again for the help. I'm getting there!
Regards,Joseph Spenner
From: Joseph Spenner joseph85...@yahoo.com
To: Rémi Cura remi.c...@gmail.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Tuesday, November 18, 2014 8:35 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Rémi: Yes, that worked perfectly. Thanks! My input poly will be stored in
another database (maybe just another table). It would be nice to perform the
query all on 1 line (get the poly and use it for the query), but I can query
the first table first to get the input poly, then use that for the 2nd query
without too much difficulty.
Thanks again for the responses!
Regards,Joseph Spenner
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Rémi Cura remi.c...@gmail.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Cc: Joseph Spenner joseph85...@yahoo.com
Sent: Tuesday, November 18, 2014 4:20 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Something like this?
SELECT zone,gid,state,name,ST_Asgeojson( geom )
FROM polys
WHERE ST_Overlaps(ST_GeomFromText('POLYGON((-109 42,-108 42, -108 43; -109 43,
-109 42))', 4269), geom)=TRUE;
Cheers,
Rémi-C
2014-11-18 9:00 GMT+01:00 Hugues François hugues.franc...@irstea.fr:
Hello, Maybe I don’t understand your problem very well but I think your query
for points should work with a polygon using ST_GeomFromText
(http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon or a
subquery if it stored into your DB. Hugues. De :
postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : lundi 17 novembre 2014 22:35
À : PostGIS Users Discussion
Objet : [postgis-users] Returning polygons if any of a polygon input points lie
in the polygons Hello, I'm trying to perform a query which will return any
polygons which have any points in common with an input polygon. So, if I have
Polygon A.In my database, I have several other polygons stored. I want to
submit a query with Polygon A as the input, which will return all of the
polygons in my database which have any points in common with Polygon A. Can
this be done?
I found this:http://postgis.net/docs/ST_Overlaps.html But from what I can tell,
it only returns a T or F. I need to fetch the actual polygons if T. I'm
currently able to query with points as input: select
zone,gid,state,name,ST_Asgeojson( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
But now I have a need to use polygons as input.
Any help would be great.
Thanks! Regards,Joseph Spenner
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Sorry for all the posts, but I think I found my answer. I can accomplish my
goal with 2 querries:
1) using the ST_Overlaps+2) using the ST_Contains
So, I just need to combine those 2 in 1 single query somehow. I'm thinking..
select ugcstring from polys where ST_Overlaps(ST_GeomFromText('POLYGON((-82
31, -88 31, -88 35, -82 35, -82 31))', 4269), geom)=TRUE or
ST_Contains(ST_GeomFromText('POLYGON((-82 31, -88 31, -88 35, -82 35, -82
31))', 4269), geom)=TRUE;
This seems to work!
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Joseph Spenner joseph85...@yahoo.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Sent: Tuesday, November 18, 2014 8:44 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Something I did notice though-- it seems that if the input polygon completely
surrounds a polygon stored in my database, it is not included in the result. I
read something about this here: http://postgis.net/docs/ST_Overlaps.html
Returns TRUE if the Geometries spatially overlap. By that we mean they
intersect, but one does not completely contain another.
I do need the polygons which are completely contained as well. Can this be
done?
Thanks again for the help. I'm getting there!
Regards,Joseph Spenner
From: Joseph Spenner joseph85...@yahoo.com
To: Rémi Cura remi.c...@gmail.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Tuesday, November 18, 2014 8:35 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Rémi: Yes, that worked perfectly. Thanks! My input poly will be stored in
another database (maybe just another table). It would be nice to perform the
query all on 1 line (get the poly and use it for the query), but I can query
the first table first to get the input poly, then use that for the 2nd query
without too much difficulty.
Thanks again for the responses!
Regards,Joseph Spenner
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Rémi Cura remi.c...@gmail.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Cc: Joseph Spenner joseph85...@yahoo.com
Sent: Tuesday, November 18, 2014 4:20 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
Something like this?
SELECT zone,gid,state,name,ST_Asgeojson( geom )
FROM polys
WHERE ST_Overlaps(ST_GeomFromText('POLYGON((-109 42,-108 42, -108 43; -109 43,
-109 42))', 4269), geom)=TRUE;
Cheers,
Rémi-C
2014-11-18 9:00 GMT+01:00 Hugues François hugues.franc...@irstea.fr:
Hello, Maybe I don’t understand your problem very well but I think your query
for points should work with a polygon using ST_GeomFromText
(http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon or a
subquery if it stored into your DB. Hugues. De :
postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : lundi 17 novembre 2014 22:35
À : PostGIS Users Discussion
Objet : [postgis-users] Returning polygons if any of a polygon input points lie
in the polygons Hello, I'm trying to perform a query which will return any
polygons which have any points in common with an input polygon. So, if I have
Polygon A.In my database, I have several other polygons stored. I want to
submit a query with Polygon A as the input, which will return all of the
polygons in my database which have any points in common with Polygon A. Can
this be done?
I found this:http://postgis.net/docs/ST_Overlaps.html But from what I can tell,
it only returns a T or F. I need to fetch the actual polygons if T. I'm
currently able to query with points as input: select
zone,gid,state,name,ST_Asgeojson( geom ) from polys where
ST_Intersects(ST_PointFromText('POINT(-109 42)', 4269), geom);
But now I have a need to use polygons as input.
Any help would be great.
Thanks! Regards,Joseph Spenner
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Returning polygons if any of a polygon input points lie in the polygons
Hugues: Yes, you are quite right. The ST_Intersects does exactly what I need.
Good stuff here.
Thanks again everyone for your patience! I probably should have tried a few
more of these before posting.
Cheers!Joseph Spenner
If life gives you lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Hugues François hugues.franc...@irstea.fr
To: Joseph Spenner joseph85...@yahoo.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Tuesday, November 18, 2014 8:52 AM
Subject: RE: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons
#yiv6953499053 #yiv6953499053 -- _filtered #yiv6953499053
{font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv6953499053
{font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv6953499053
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv6953499053
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv6953499053
{font-family:Garamond;panose-1:2 2 4 4 3 3 1 1 8 3;}#yiv6953499053
#yiv6953499053 p.yiv6953499053MsoNormal, #yiv6953499053
li.yiv6953499053MsoNormal, #yiv6953499053 div.yiv6953499053MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv6953499053 a:link,
#yiv6953499053 span.yiv6953499053MsoHyperlink
{color:blue;text-decoration:underline;}#yiv6953499053 a:visited, #yiv6953499053
span.yiv6953499053MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv6953499053
p.yiv6953499053MsoAcetate, #yiv6953499053 li.yiv6953499053MsoAcetate,
#yiv6953499053 div.yiv6953499053MsoAcetate
{margin:0cm;margin-bottom:.0001pt;font-size:8.0pt;}#yiv6953499053
span.yiv6953499053EmailStyle17 {color:#1F497D;}#yiv6953499053
span.yiv6953499053TextedebullesCar {}#yiv6953499053 .yiv6953499053MsoChpDefault
{font-size:10.0pt;} _filtered #yiv6953499053 {margin:70.85pt 70.85pt 70.85pt
70.85pt;}#yiv6953499053 div.yiv6953499053WordSection1 {}#yiv6953499053 Hi, I
think ST_Intersects will meet your needs : SELECT
zone,gid,state,name,ST_Asgeojson( geom ) FROM polys WHERE
ST_Intersects(ST_GeomFromText('POLYGON((-109 42,-108 42, -108 43; -109 43, -109
42))', 4269), geom) Hugues. Hugues FrançoisTourisme et systèmes
d’informationIrstea – DTM +33 (0)4.76.76.27.44+33 (0)6.77.66.21.31 De :
postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : mardi 18 novembre 2014 16:44
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Returning polygons if any of a polygon input points
lie in the polygons
Something I did notice though-- it seems that if the input polygon completely
surrounds a polygon stored in my database, it is not included in the result. I
read something about this here: http://postgis.net/docs/ST_Overlaps.html
Returns TRUE if the Geometries spatially overlap. By that we mean they
intersect, but one does not completely contain another. I do need the
polygons which are completely contained as well. Can this be done? Thanks
again for the help. I'm getting there! Regards,Joseph Spenner From:
Joseph Spenner joseph85...@yahoo.com
To: Rémi Cura remi.c...@gmail.com; PostGIS Users Discussion
postgis-users@lists.osgeo.org
Sent: Tuesday, November 18, 2014 8:35 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons Rémi: Yes, that worked perfectly. Thanks! My
input poly will be stored in another database (maybe just another table). It
would be nice to perform the query all on 1 line (get the poly and use it for
the query), but I can query the first table first to get the input poly, then
use that for the 2nd query without too much difficulty.
Thanks again for the responses! Regards,Joseph Spenner If life gives you
lemons, keep them-- because hey.. free lemons.
~heart~ Sticker fixer: http://microflush.org/stuff/stickers/heartFix.html
From: Rémi Cura remi.c...@gmail.com
To: PostGIS Users Discussion postgis-users@lists.osgeo.org
Cc: Joseph Spenner joseph85...@yahoo.com
Sent: Tuesday, November 18, 2014 4:20 AM
Subject: Re: [postgis-users] Returning polygons if any of a polygon input
points lie in the polygons Something like this?
SELECT zone,gid,state,name,ST_Asgeojson( geom )
FROM polys WHERE ST_Overlaps(ST_GeomFromText('POLYGON((-109 42,-108 42, -108
43; -109 43, -109 42))', 4269), geom)=TRUE; Cheers,
Rémi-C 2014-11-18 9:00 GMT+01:00 Hugues François hugues.franc...@irstea.fr:
Hello, Maybe I don’t understand your problem very well but I think your query
for points should work with a polygon using ST_GeomFromText
(http://www.postgis.org/docs/ST_GeomFromText.html) for your input polygon or a
subquery if it stored into your DB. Hugues. De :
postgis-users-boun...@lists.osgeo.org
[mailto:postgis-users-boun...@lists.osgeo.org] De la part de Joseph Spenner
Envoyé : lundi 17
Re: [postgis-users] Topology: cannot delete slivers (or gaps)
On Wed, Nov 12, 2014 at 10:51:34AM +0100, Rémi Cura wrote: Note you could have directly exported into postgis topology, which is slow but very convenient (stil faster than postgis topology conversion). I'm not a grass user, from what I understood you have to use the -l switch in the command *v.out.postgis* I'm trying this out today, and indeed is very slow, much slower than building the topology, which is surprising to me. A look at database activity shows that the command (v.out.postgis -l) is writing _simple_ features in the output layer. Do you have an idea about how to skip that part ? (TopoGeometry would be enough for PostGIS to reconstruct the SFS on demand). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Topology: cannot delete slivers (or gaps)
This is suprising indeed. Im my test using the v.out.postgis was still way faster than constructing with postgis topology (about x3 I think). Are you using v.out.postgis -l, with grass 7? Grass is a topology that is more node centered, opposite to postgis topology which is more edge centered. It is possible to output a node centered topology with grass as text (*v.info http://v.info -t*), but I found it unpractical to try to convert this into a propoer postgis topology with pure SQL. Now I think I should have used a python way from within grass (grass offers full binding, so it should be doable to loop trough grass internal topology and convert it to postgis topology) I tried to reach grass dev several time to try to improve the postgis export, I had no answer. My intuition is that the slowness comes from the fact that grass tries to write edge by edge , and each time the edge_data trigger are triggered. The correct way would be to compute full topology structure, then deactivate trigger, then fill table , then reactivate trigger. Cheers, Rémi-C 2014-11-18 19:48 GMT+01:00 Sandro Santilli s...@keybit.net: On Wed, Nov 12, 2014 at 10:51:34AM +0100, Rémi Cura wrote: Note you could have directly exported into postgis topology, which is slow but very convenient (stil faster than postgis topology conversion). I'm not a grass user, from what I understood you have to use the -l switch in the command *v.out.postgis* I'm trying this out today, and indeed is very slow, much slower than building the topology, which is surprising to me. A look at database activity shows that the command (v.out.postgis -l) is writing _simple_ features in the output layer. Do you have an idea about how to skip that part ? (TopoGeometry would be enough for PostGIS to reconstruct the SFS on demand). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
