Re: [postgis-users] st_intersects() returns false with point and polygon that obviously intersect
On Thu, Feb 24, 2011 at 12:58:24PM +1300, Craig de Stigter wrote:
Hi list
This query returns false, but the box and point intersect:
select st_intersects('POINT(169.69960846592 -46.5061209281002)'::geometry,
'POLYGON((169.699607857174 -46.5061218662,169.699607857174
-46.5061195965597,169.699608806526 -46.5061195965597,169.699608806526
-46.5061218662,169.699607857174 -46.5061218662))'::geometry);
st_intersects
Does sound like a bug:
st_relate | st_intersects | st_disjoint
---+---+-
0F212 | f | f
ST_Relate is correct, ST_Disjoint is correct, ST_Intersects is _wrong_
I B E
-
I 0 F F
B F F F
E 2 1 2
Could you please file a bug for this ?
http://trac.osgeo.org/postgis
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_intersects() returns false with point and polygon that obviously intersect
Thanks for the confirmation. Created
#845http://trac.osgeo.org/postgis/ticket/845
On Thu, Feb 24, 2011 at 9:41 PM, Sandro Santilli s...@keybit.net wrote:
On Thu, Feb 24, 2011 at 09:35:44AM +0100, Sandro Santilli wrote:
On Thu, Feb 24, 2011 at 12:58:24PM +1300, Craig de Stigter wrote:
Hi list
This query returns false, but the box and point intersect:
select st_intersects('POINT(169.69960846592
-46.5061209281002)'::geometry,
'POLYGON((169.699607857174 -46.5061218662,169.699607857174
-46.5061195965597,169.699608806526 -46.5061195965597,169.699608806526
-46.5061218662,169.699607857174 -46.5061218662))'::geometry);
st_intersects
Does sound like a bug:
st_relate | st_intersects | st_disjoint
---+---+-
0F212 | f | f
ST_Relate is correct, ST_Disjoint is correct, ST_Intersects is _wrong_
I B E
-
I 0 F F
B F F F
E 2 1 2
Could you please file a bug for this ?
http://trac.osgeo.org/postgis
For the record: the GEOS side is fine, so must be some shortcut in postgis.
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
--
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problem transferring geometries from 1.5 to 1.3
Hello, i stumbled into a problem when i tried to insert geometries from an 1.5 installation to a postgis 1.3. The 1.5 is my local test server, the 1.3 the distant production server, to which i only have a FTP access, so i'm uploading geometries with the help of a PHP script reading a big csv (text) file. The geometries are MULTIPOLYGONS with holes. When i try to insert some of the geometries (very complicated ones), i got an error : ERROR : invalid geometry. The format used to represent the geometries is the default hexewkb. The 1.3 install full version is : POSTGIS=1.3.2 GEOS=2.2.1-CAPI-1.0.1 PROJ=Rel. 4.4.9, 29 Oct 2004 USE_STATS The 1.5 install full version is : POSTGIS=1.5.0 GEOS=3.2.2-CAPI-1.6.2 PROJ=Rel. 4.7.1, 23 September 2009 LIBXML=2.7.3 USE_STATS Is there a way to convert those geometries ? Thanks for your help, best regards, L. Jégou -- Laurent Jégou Cartographe et enseignant UTM - Dépt. Géographie 31058 TOULOUSE Cedex 9 - 05.61.50.43.89 http://www.univ-tlse2.fr/geoprdc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Value from Polygon
Thanks! That solved it. This will probably take a lot of time. I have 259200 polygons measuring 0.5 x 0.5 decimal degrees while the raster dataset is of global cover and has a pixelsize of 0.00278x0.00278. Andreas 2011/2/23 Paragon Corporation l...@pcorp.us Andrea, Try SELECT DISTINCT ON(gid) gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val ORDER BY gid, ct DESC -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas Forø Tollefsen *Sent:* Wednesday, February 23, 2011 4:05 AM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] ST_Value from Polygon Hi. Thanks Regina and Leo, I have been testing the raster and geom intersection a bit. I guess what i need is to use the ST_Intersection together with a max(count) function. So my result will be the rastervalue with the highest count within each of the grid cells. However, as far as i know, there is now Max(COUNT) function in postgresql. Any idea how i can modify the below query to only return the rastervalue within the grid cell occuring most frequently? Consequently i want only one row for each gid, and the maximum occuring rastervalue. SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val; gid; val; ct 151001;14;381 151001;150;9 151001;50;7 151001;140;91 151001;40;1 151001;70;2 151001;130;4 151001;200;48 151001;100;3 151001;;0 151001;190;1 151001;20;203 151001;11;111 151001;210;16 151001;30;105 2011/2/23 Paragon Corporation l...@pcorp.us Have you looked at ST_Intersection. I'm not sure how large your grids are so might still be a bit too slow. http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html Below is a link to our slides from our North Carolina GIS meeting that may answer some of your questions (shows some Raster examples) as well as the 3D ones people have asked. http://www.postgis.us/presentations Hope that helps, Regina and Leo -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas Forø Tollefsen *Sent:* Tuesday, February 22, 2011 4:28 AM *To:* PostGIS Users Discussion *Subject:* [postgis-users] ST_Value from Polygon Hi all, I am working with a large raster dataset that i want to aggregate into vector grids. The raster dataset is a landcover dataset, and i want to find which of the raster values are the most dominant within each of the vector grid cells. I have been looking at the ST_Value function, but this is not usable together with the cell polygon. I have written a script that gives me the raster value of the centroid of each cell, but i want to find which raster class is the largest. Hence i need to calculate the area of each raster class within each cell and select the largest class. Any idea? So far i have only come this far: DROP TABLE IF EXISTS globshortpoly; SELECT priogrid_land.cell, ST_Value(rast, ST_Centroid(cell)) INTO globshortpoly FROM priogrid_land, globshort WHERE rast priogrid_land.cell LIMIT 1000 ___ 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] Problem transferring geometries from 1.5 to 1.3
Hello, i was able to solve the problem. With the help of Fred Lehodey i found that the geometries were not valid, some inner rings intersections (with isvalidreason(the_geom)). I used the very neat function from Regina Obe : http://postgis.refractions.net/pipermail/postgis-users/2009-March/023064.html And now all is well :-) Thanks Fred and Regina, L. Jégou -- Laurent Jégou Cartographe et enseignant UTM - Dépt. Géographie 31058 TOULOUSE Cedex 9 - 05.61.50.43.89 http://www.univ-tlse2.fr/geoprdc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Value from Polygon
Andreas, Sorry should have recognized what you're doing. The intersection returns a polygon which is a union of the clipped raster pixel squares. So you need to use Sum of area instead and then divide by the area of a pixel to get the equivalent of your count. So SELECT gid, SUM(ST_Area((foo.geomval).geom))/ [put your pixel area size here] as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid _ From: Andreas Forø Tollefsen [mailto:andrea...@gmail.com] Sent: Thursday, February 24, 2011 8:33 AM To: PostGIS Users Discussion Cc: Paragon Corporation Subject: Re: [postgis-users] ST_Value from Polygon I am a bit unsure whether my results are actually correct. According to a total count using the below query, I get very different results between the cells. Since the raster does actually cover the whole vector cell, i would assume that the count should be similar in all cells. Meaning, the pixel count should be the same. What i get is different, and it seems that the query is not providing me with the number of pixels within the grid cell. Any idea why this is so different? SELECT gid, count((foo.geomval).val) as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid Result: 139358;632 139359;1030 139360;912 139361;731 139362;760 139363;1230 139364;1314 139365;1014 The attached image shows the raster pixels within one cell. 2011/2/24 Andreas Forø Tollefsen andrea...@gmail.com Thanks! That solved it. This will probably take a lot of time. I have 259200 polygons measuring 0.5 x 0.5 decimal degrees while the raster dataset is of global cover and has a pixelsize of 0.00278x0.00278. Andreas 2011/2/23 Paragon Corporation l...@pcorp.us Andrea, Try SELECT DISTINCT ON(gid) gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val ORDER BY gid, ct DESC _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: Wednesday, February 23, 2011 4:05 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Value from Polygon Hi. Thanks Regina and Leo, I have been testing the raster and geom intersection a bit. I guess what i need is to use the ST_Intersection together with a max(count) function. So my result will be the rastervalue with the highest count within each of the grid cells. However, as far as i know, there is now Max(COUNT) function in postgresql. Any idea how i can modify the below query to only return the rastervalue within the grid cell occuring most frequently? Consequently i want only one row for each gid, and the maximum occuring rastervalue. SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val; gid; val; ct 151001;14;381 151001;150;9 151001;50;7 151001;140;91 151001;40;1 151001;70;2 151001;130;4 151001;200;48 151001;100;3 151001;;0 151001;190;1 151001;20;203 151001;11;111 151001;210;16 151001;30;105 2011/2/23 Paragon Corporation l...@pcorp.us Have you looked at ST_Intersection. I'm not sure how large your grids are so might still be a bit too slow. http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html Below is a link to our slides from our North Carolina GIS meeting that may answer some of your questions (shows some Raster examples) as well as the 3D ones people have asked. http://www.postgis.us/presentations Hope that helps, Regina and Leo _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: Tuesday, February 22, 2011 4:28 AM To: PostGIS Users Discussion Subject: [postgis-users] ST_Value from Polygon Hi all, I am working with a large raster dataset that i want to aggregate into vector grids. The raster dataset is a landcover dataset, and i want to find which of the raster values are the most dominant within each of the vector grid cells. I have been looking at the ST_Value function, but this is not usable together with the cell polygon. I have written a script that gives me the raster value of the centroid of each cell, but i want to find which raster class is the
Re: [postgis-users] ST_Value from Polygon
Great. Thank you so much. I should have noticed that these were unioned numbers. Btw. are there any way of getting the count of pixels within a polygon without actually aggregating them or union them? Andreas 2011/2/24 Paragon Corporation l...@pcorp.us Andreas, Sorry should have recognized what you're doing. The intersection returns a polygon which is a union of the clipped raster pixel squares. So you need to use Sum of area instead and then divide by the area of a pixel to get the equivalent of your count. So SELECT gid, SUM(ST_Area((foo.geomval).geom))/ [put your pixel area size here] as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid -- *From:* Andreas Forø Tollefsen [mailto:andrea...@gmail.com] *Sent:* Thursday, February 24, 2011 8:33 AM *To:* PostGIS Users Discussion *Cc:* Paragon Corporation *Subject:* Re: [postgis-users] ST_Value from Polygon I am a bit unsure whether my results are actually correct. According to a total count using the below query, I get very different results between the cells. Since the raster does actually cover the whole vector cell, i would assume that the count should be similar in all cells. Meaning, the pixel count should be the same. What i get is different, and it seems that the query is not providing me with the number of pixels within the grid cell. Any idea why this is so different? SELECT gid, count((foo.geomval).val) as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid Result: 139358;632 139359;1030 139360;912 139361;731 139362;760 139363;1230 139364;1314 139365;1014 The attached image shows the raster pixels within one cell. 2011/2/24 Andreas Forø Tollefsen andrea...@gmail.com Thanks! That solved it. This will probably take a lot of time. I have 259200 polygons measuring 0.5 x 0.5 decimal degrees while the raster dataset is of global cover and has a pixelsize of 0.00278x0.00278. Andreas 2011/2/23 Paragon Corporation l...@pcorp.us Andrea, Try SELECT DISTINCT ON(gid) gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val ORDER BY gid, ct DESC -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas Forø Tollefsen *Sent:* Wednesday, February 23, 2011 4:05 AM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] ST_Value from Polygon Hi. Thanks Regina and Leo, I have been testing the raster and geom intersection a bit. I guess what i need is to use the ST_Intersection together with a max(count) function. So my result will be the rastervalue with the highest count within each of the grid cells. However, as far as i know, there is now Max(COUNT) function in postgresql. Any idea how i can modify the below query to only return the rastervalue within the grid cell occuring most frequently? Consequently i want only one row for each gid, and the maximum occuring rastervalue. SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val; gid; val; ct 151001;14;381 151001;150;9 151001;50;7 151001;140;91 151001;40;1 151001;70;2 151001;130;4 151001;200;48 151001;100;3 151001;;0 151001;190;1 151001;20;203 151001;11;111 151001;210;16 151001;30;105 2011/2/23 Paragon Corporation l...@pcorp.us Have you looked at ST_Intersection. I'm not sure how large your grids are so might still be a bit too slow. http://www.postgis.org/documentation/manual-svn/RT_ST_Intersection.html Below is a link to our slides from our North Carolina GIS meeting that may answer some of your questions (shows some Raster examples) as well as the 3D ones people have asked. http://www.postgis.us/presentations Hope that helps, Regina and Leo -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas Forø Tollefsen *Sent:* Tuesday, February 22, 2011 4:28 AM *To:* PostGIS Users Discussion *Subject:* [postgis-users] ST_Value from Polygon Hi all, I am
[postgis-users] regarding count query on multiple column in one go
Hi All, I have a table hydro_net in PostGIS database. The table has following schema: CREATE TABLE public.hydro_net (hid integer NOT NULL DEFAULT nextval, f_code_des varchar(254), hyd_desc varchar(254), hyd_name varchar(254), geom geometry, I have more than 3000 records in this table and some records have multiple hyd_name but with the same geometry. I can count the hyd_name with a simple query to get the hyd_name and the count as under: select hyd_name, count(*) from hydro_net group by hyd_name having count(*) 1 order by hyd_name; I would like to know if it is possible to get the table out all the columns in the original table with the records of duplicate hyd_name and its count. I am not really getting how to write a query that count the hyd_name and also returns all column information as well at least hyd_name, geom and count. Would really appreciate any help. Thanks, Yamini___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Value from Polygon
You should just divide the area of your polygon by the area of one of your pixel. I want to investigate this example a little bit further. What is the size of your raster in pixels? I understand that you did not tile it. Did you? Pierre From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: 24 février 2011 09:53 To: Paragon Corporation Cc: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Value from Polygon Great. Thank you so much. I should have noticed that these were unioned numbers. Btw. are there any way of getting the count of pixels within a polygon without actually aggregating them or union them? Andreas 2011/2/24 Paragon Corporation l...@pcorp.usmailto:l...@pcorp.us Andreas, Sorry should have recognized what you're doing. The intersection returns a polygon which is a union of the clipped raster pixel squares. So you need to use Sum of area instead and then divide by the area of a pixel to get the equivalent of your count. So SELECT gid, SUM(ST_Area((foo.geomval).geom))/ [put your pixel area size here] as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid From: Andreas Forø Tollefsen [mailto:andrea...@gmail.commailto:andrea...@gmail.com] Sent: Thursday, February 24, 2011 8:33 AM To: PostGIS Users Discussion Cc: Paragon Corporation Subject: Re: [postgis-users] ST_Value from Polygon I am a bit unsure whether my results are actually correct. According to a total count using the below query, I get very different results between the cells. Since the raster does actually cover the whole vector cell, i would assume that the count should be similar in all cells. Meaning, the pixel count should be the same. What i get is different, and it seems that the query is not providing me with the number of pixels within the grid cell. Any idea why this is so different? SELECT gid, count((foo.geomval).val) as ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid = 139358 AND gid = 139365 GROUP BY gid ORDER BY gid Result: 139358;632 139359;1030 139360;912 139361;731 139362;760 139363;1230 139364;1314 139365;1014 The attached image shows the raster pixels within one cell. 2011/2/24 Andreas Forø Tollefsen andrea...@gmail.commailto:andrea...@gmail.com Thanks! That solved it. This will probably take a lot of time. I have 259200 polygons measuring 0.5 x 0.5 decimal degrees while the raster dataset is of global cover and has a pixelsize of 0.00278x0.00278. Andreas 2011/2/23 Paragon Corporation l...@pcorp.usmailto:l...@pcorp.us Andrea, Try SELECT DISTINCT ON(gid) gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val ORDER BY gid, ct DESC From: postgis-users-boun...@postgis.refractions.netmailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.netmailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: Wednesday, February 23, 2011 4:05 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Value from Polygon Hi. Thanks Regina and Leo, I have been testing the raster and geom intersection a bit. I guess what i need is to use the ST_Intersection together with a max(count) function. So my result will be the rastervalue with the highest count within each of the grid cells. However, as far as i know, there is now Max(COUNT) function in postgresql. Any idea how i can modify the below query to only return the rastervalue within the grid cell occuring most frequently? Consequently i want only one row for each gid, and the maximum occuring rastervalue. SELECT gid, (foo.geomval).val, COUNT((foo.geomval).val) AS ct FROM (SELECT globshort.rid, priogrid_land.cell, priogrid_land.gid, ST_Intersection(globshort.rast, priogrid_land.cell) AS geomval FROM globshort, priogrid_land) AS foo WHERE gid 151000 AND gid 151010 GROUP BY gid, (foo.geomval).val; gid; val; ct 151001;14;381 151001;150;9 151001;50;7 151001;140;91 151001;40;1 151001;70;2 151001;130;4 151001;200;48 151001;100;3 151001;;0 151001;190;1 151001;20;203 151001;11;111 151001;210;16 151001;30;105 2011/2/23 Paragon Corporation l...@pcorp.usmailto:l...@pcorp.us Have you looked at ST_Intersection. I'm not sure how large your grids are so might still be a bit too slow.
[postgis-users] Sponsorship for OSGeo Montreal Code Sprint 2011
Dear PostGIS users, OSGeo's Montreal Code Sprint 2011 is going to take place on March 15-18, 2011. A group of over 20 OSGeo project developers and contributors will meet in Montreal for 4 days of work on their respective projects... for the advancement of the software that you use everyday. So far, 22 devs/contributors have signed up to participate, including 5 who plan to work on PostGIS. If your organization uses PostGIS then you might want to let your boss know (or take note if you're the boss) that we are looking for sponsors ($750 each) to support food and fun for the sprinters as they work hard and play hard for four productive days. Each $750 sponsorship will be put towards lunch and dinner costs for the sprinters, and potentially bringing in sprinters who might otherwise be unable to attend. Ideally we'd need another 2 or 3 sponsors to meet our current budget objectives, and if we have a surplus then it will either be returned to OSGeo, or used for a future code sprint. All the details about the sprint, including our current budget are available in the wiki at http://wiki.osgeo.org/wiki/Montreal_Code_Sprint_2011 If you are interested in sponsoring then please contact me directly off-list. Finally, I'd like to thank our first round of sponsors: - LizardTech - http://www.lizardtech.com/ - Azavea - http://www.azavea.com/ - qPublic - http://qpublic.net/ - Farallon Geographics - http://fargeo.com/ And also big thanks to the Communauté Métropolitaine de Montréal (CMM) for hosting us in their wonderful conference room. I hope to be able to announce a few more sponsors in the next couple of days. :-) Daniel P.S. There is also still a bit of room for more sprinters. The only requirement is that you be knowledgeable and willing to get your hands dirty doing some work for one of the projects that is represented. If you are interested in participating then to help us with planning please list your name in the wiki, join the tosprint list to receive the latest updates, and book your hotel ASAP since our room block is about to expire. All that info is in the wiki. -- Daniel Morissette http://www.mapgears.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] regarding count query on multiple column in one go
Yamini, The first way that comes to mind is just wrap that in a subselect. SELECT orig.* FROM hydro_net As orig INNER JOIN ( select hyd_name, count(*) from hydro_net group by hyd_name having count(*) 1) As dupes ON (orig.hyd_name = dupes.hyd_name) Leo http://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Yamini Singh Sent: Thursday, February 24, 2011 11:19 AM To: PostGIS User List Subject: [postgis-users] regarding count query on multiple column in one go Hi All, I have a table hydro_net in PostGIS database. The table has following schema: CREATE TABLE public.hydro_net (hid integer NOT NULL DEFAULT nextval, f_code_des varchar(254), hyd_desc varchar(254), hyd_name varchar(254), geom geometry, I have more than 3000 records in this table and some records have multiple hyd_name but with the same geometry. I can count the hyd_name with a simple query to get the hyd_name and the count as under: select hyd_name, count(*) from hydro_net group by hyd_name having count(*) 1 order by hyd_name; I would like to know if it is possible to get the table out all the columns in the original table with the records of duplicate hyd_name and its count. I am not really getting how to write a query that count the hyd_name and also returns all column information as well at least hyd_name, geom and count. Would really appreciate any help. Thanks, Yamini ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Find n Nearest Neighbors for given Point using PostGIS?
I am trying to solve the problem of finding the n nearest neighbors using PostGIS: Starting Point: - Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84) - Added a GeometryColumn geom with srid=4326 and datatype=POINT - Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude) 4326); - Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;) - Created PRIMARY KEY UNIQUE BTREE index for geonameid Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid. Possible solution: Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid + AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5 Processing time: about 60s Also tried an approach based on EXPAND: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid AND expand(start.geom, 300) ende.geom + order by distance limit 5 Processing time: about 120s The intended application is some kind of autocomplete. So, any approach taking longer than 1s is not applicable. Is it generally possible to achieve such a response time with PostGIS? -- View this message in context: http://old.nabble.com/Find-n-Nearest-Neighbors-for-given-Point-using-PostGIS--tp31010122p31010122.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Find n Nearest Neighbors for given Point using PostGIS?
Have you tried EXPLAIN to see where the slow part is? But at a guess - consider that st_dwithin uses the geometry unit for it's calculations - so you are searching for everything within 300 degrees (more than halfway around the planet). You may want to try searching a smaller set of data before you sort it to find the closest five. cheers Ben On 25/02/2011, at 12:04 PM, Scholle wrote: I am trying to solve the problem of finding the n nearest neighbors using PostGIS: Starting Point: - Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84) - Added a GeometryColumn geom with srid=4326 and datatype=POINT - Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude) 4326); - Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;) - Created PRIMARY KEY UNIQUE BTREE index for geonameid Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid. Possible solution: Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid + AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5 Processing time: about 60s Also tried an approach based on EXPAND: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid AND expand(start.geom, 300) ende.geom + order by distance limit 5 Processing time: about 120s The intended application is some kind of autocomplete. So, any approach taking longer than 1s is not applicable. Is it generally possible to achieve such a response time with PostGIS? -- View this message in context: http://old.nabble.com/Find-n-Nearest-Neighbors-for-given-Point-using-PostGIS--tp31010122p31010122.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ 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] SPAM blocking on postgis server
G'day all, I just tried to send a response, and my ISP (who's SMTP I normally use) rejected it : Reason:5.7.1 Service unavailable; Client host [61.9.189.137] blocked using dnsbl.sorbs.net; Currently Sending Spam See:http://www.sorbs.net/lookup.shtml?61.9.189.137 I doubt my ISP really cares, but it might affect the list? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Find n Nearest Neighbors for given Point using PostGIS?
Great, didn't consider the geometry/degree difference I drastically decreased the value for the third parameter of ST_DWithin function and its sufficiently fast now... Ben Madin-3 wrote: Have you tried EXPLAIN to see where the slow part is? But at a guess - consider that st_dwithin uses the geometry unit for it's calculations - so you are searching for everything within 300 degrees (more than halfway around the planet). You may want to try searching a smaller set of data before you sort it to find the closest five. cheers Ben On 25/02/2011, at 12:04 PM, Scholle wrote: I am trying to solve the problem of finding the n nearest neighbors using PostGIS: Starting Point: - Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84) - Added a GeometryColumn geom with srid=4326 and datatype=POINT - Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude) 4326); - Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;) - Created PRIMARY KEY UNIQUE BTREE index for geonameid Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid. Possible solution: Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid + AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5 Processing time: about 60s Also tried an approach based on EXPAND: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid AND expand(start.geom, 300) ende.geom + order by distance limit 5 Processing time: about 120s The intended application is some kind of autocomplete. So, any approach taking longer than 1s is not applicable. Is it generally possible to achieve such a response time with PostGIS? -- View this message in context: http://old.nabble.com/Find-n-Nearest-Neighbors-for-given-Point-using-PostGIS--tp31010122p31010122.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ 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 -- View this message in context: http://old.nabble.com/Find-n-Nearest-Neighbors-for-given-Point-using-PostGIS--tp31010122p31010203.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Find n Nearest Neighbors for given Point using PostGIS?
Scholle, The only way you can do nearest neighbor searches the are fast is to write a stored procedure that expands the search if you fail to get the number of results you want. So in pseudo code something like: radius := 0.01; -- assuming degrees loop select into cnt count(*) from mytable where expand(mypnt, radius) the_geom limit 5; if found and cnt = 5 or radius maxradius then for rr in select * from mytable where expand(mypnt, radius) the_geom limit 5 loop return rr; end loop; return; else radius := radius * 2; end if; end loop; So make a set returning function with the body something like this and you should get good performance. Because postgresql, does a really good job of caching pages and query results you will not pay much of a penalty for the repeated queries. -Steve On 2/24/2011 11:04 PM, Scholle wrote: I am trying to solve the problem of finding the n nearest neighbors using PostGIS: Starting Point: - Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84) - Added a GeometryColumn geom with srid=4326 and datatype=POINT - Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude) 4326); - Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;) - Created PRIMARY KEY UNIQUE BTREE index for geonameid Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid. Possible solution: Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid + AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5 Processing time: about 60s Also tried an approach based on EXPAND: SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance + FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid ende.geonameid AND expand(start.geom, 300) ende.geom + order by distance limit 5 The problem here is that you are expanding your search by 300 degrees if your data is in WGS84. Processing time: about 120s The intended application is some kind of autocomplete. So, any approach taking longer than1s is not applicable. Is it generally possible to achieve such a response time with PostGIS? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
