<HTML dir=ltr><HEAD><TITLE>RE: [postgis-users] point-in-polygon SQL sentence performance</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3354" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText10229 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Pere,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>I'm still not clear why you are creating an individual user_points table for each and deleting it. I think I understand a little better, but I'm still guessing a bit.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Below is how I would do it without creating a temp table for each user. By the way ST_Contains is a simpler way of achieving for A && B And Contains(A,B)</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>INSERT INTO user_points(<FONT face="Times New Roman">geom, specie,genus,code, userid, the_geom)</FONT></FONT></DIV>
<DIV dir=ltr><FONT size=2>SELECT p.geom, p.specie, p.genus, poly.code, p.userid, p.the_geom</FONT></DIV>
<DIV dir=ltr><FONT size=2>FROM points p </FONT><FONT size=2>INNER JOIN polygon poly ON ST_Contains(poly.the_geom, p.the_geom)</FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE p.userid = '$user' </FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>(You can leave the WHERE out if you plan to do it for all users - it will save you the trouble to just do it in one step. Although sounds like you maybe creating these temp tables for the logged in user)</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>You may also want to replace ST_Contains with ST_Intersects (I'm nost sure about performance differences). although not sure how all these work in long lat since they assume cartesian rather than spherical. I think it should be fine.</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>--Next Polygon part - I am assuming genus, species etc are counts (or are those just words) at each point location and you want for each</FONT></DIV>
<DIV dir=ltr><FONT size=2>user to count the total of each they captured in each (you would use SUM not COUNT for this - this is just a guess on my part - they may be actual genus, species etc - in which case just throw out those fields)</FONT></DIV>
<DIV dir=ltr><FONT size=2>--To make this efficient, you will want to put a btree index on user_points.code and polygon.poly_code</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>INSERT INTO user_polygon (userid, polygon_code,the_geom, numrecords,numgenus, numspecies)<BR>SELECT p.userid, polygon.polygon_code, polygon.the_geom, COUNT(p.code),</FONT></DIV>
<DIV dir=ltr><FONT size=2>SUM(genus), SUM(species)</FONT></DIV>
<DIV dir=ltr><FONT size=2>FROM user_points p</FONT></DIV>
<DIV dir=ltr><FONT size=2> INNER JOIN polygon ON p.code = polygon.poly_code</FONT></DIV>
<DIV dir=ltr><FONT size=2>GROUP BY p.userid, polygon.polygon_code, polygon.the_geom;</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>---If you don't really need user_points except for rolling up to user_polygon, then you can forget about all I said above and just do a one statement sql</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>
<DIV dir=ltr><FONT size=2>INSERT INTO user_polygon (userid, polygon_code,the_geom, numrecords,numgenus, numspecies)<BR>SELECT p.userid, polygon.polygon_code, polygon.the_geom, COUNT(p.code),</FONT></DIV>
<DIV dir=ltr><FONT size=2>SUM(genus), SUM(species)</FONT></DIV>
<DIV dir=ltr><FONT size=2>FROM user_points p</FONT></DIV>
<DIV dir=ltr><FONT size=2> INNER JOIN polygon ON ST_Contains(polygon.the_geom, p.the_geom)</FONT></DIV>
<DIV dir=ltr><FONT size=2>GROUP BY p.userid, polygon.polygon_code, polygon.the_geom;</FONT></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>(normally I would frown on putting the_geom in a group by since it does an extent like grouping, but in this</DIV>
<DIV dir=ltr>case I think its fine because your polygon.polygon_code will guarantee uniqueness. So the extra the_geom is a necessary but redundant grouping)</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>IF you still need to do an update - some how you took my example and rewoded it so it does something completely different from what I had intended. It should read like below not what you reiterated (e.g. standard form is</DIV>
<DIV dir=ltr>UPDATE sometable1 </DIV>
<DIV dir=ltr> FROM sometable2 </DIV>
<DIV dir=ltr> WHERE sometable1.somefield1 = sometable2.somefield2 </DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>my sometable2 in this case is a subselect aliased as u</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>(SELECT<BR> COUNT(user_point.code) as cnt, user_point.code </DIV>
<DIV dir=ltr> FROM user_point GROUP BY<BR>user_point.code) as u ;</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>---</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr> UPDATE user_polygon SET numtax = u.cnt </DIV>
<DIV dir=ltr>FROM (SELECT<BR> COUNT(user_point.code) as cnt, user_point.code </DIV>
<DIV dir=ltr> FROM user_point GROUP BY<BR>user_point.code) as u </DIV>
<DIV dir=ltr>WHERE u.code = user_polygon.code;<BR></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Hope that helps,</DIV>
<DIV dir=ltr>Regina</DIV></FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of pere roca<BR><B>Sent:</B> Mon 6/30/2008 12:40 PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B> RE: [postgis-users] point-in-polygon SQL sentence performance<BR></FONT><BR></DIV>
<DIV><BR><BR>
<P><FONT size=2> dear Regina,<BR><BR> first, thanks to take some time.<BR> yes, it's a little complicated to explain; I will try again:<BR> -My webapp lets user insert point data; all users insert this data in the<BR>same table (point)<BR> -I have several polygon tables in postGIS (to cross with point data)<BR><BR>I thought about:<BR><BR>Updating an unique,exclusive user_point table with fields: geom, specie,<BR>genus... and code. All fields except the code are extracted from point table<BR>using $user as identifier ($user comes to PHP from user identification). The<BR>code for each point is extracted using CONTAINS and && sentence (see the PHP<BR>script) from polygon table.<BR><BR>A user_polygon table will have the geom, id... and fields like<BR>number_of_records (see SET numtax below), number_genera that require<BR>counting how many records fall inside EACH polygon. This fields are filled<BR>with the COUNT sentences taking into consideration the previous point table<BR>updating.<BR><BR>I've tried the sentence below, following your code:<BR> UPDATE user_polygon SET numtax = (select u.cnt from (SELECT<BR>COUNT(user_point.code) as cnt, user_point.code FROM user_point GROUP BY<BR>user_point.code) as u WHERE u.code = user_polygon.code);";<BR><BR>The sentence inside (select u.cnt...) goes very very fast, but "loses" a<BR>lot of time (also takes 1min 20sec) comparing WHERE u.code =<BR>user_polygon.code. I think this is the problem, but there is some solution<BR>to this? isn't the main idea good? everything goes fast except this last<BR>part.<BR><BR>After executing all the SQL sentences, data from user_point and<BR>user_polygon is deleted (but not the table!).<BR><BR>Some answers:<BR>-"1) Are you using inherited tables and having a different table for each<BR>user?". No inherited tables (was wrong using ONLY parameter). A user = a<BR>point and a polygon table (info deleted each time session expires)<BR>-"How big is your user_polygon table". My testings are based on a 20 Mb<BR>"Toast table size", 544 Kb Table size. It has 1991 records.<BR><BR> Thanks again<BR> <BR> Pere Roca<BR><BR>Obe, Regina DND\MIS wrote:<BR>><BR>> Pere,<BR>><BR>> Somehow I think your whole code is needlessly complicated, but I still<BR>> don't have a clear idea what you are trying to do and looking at your<BR>> code is actually more confusing than anything. Maybe try to restate<BR>> exactly what you are trying to do minus the code. <BR>><BR>> I'm guessing you are trying to do this (by the way wrapping a (SELECT<BR>> ...) like that is very non-standard)<BR>><BR>> INSERT INTO user_polygon (userid, polygon_code,the_geom)<BR>> SELECT<BR>> distinct(polygon_code),the_geom FROM polygon where polygon."."the_geom<BR>> &&<BR>> GeometryFromText('".$row['astext']."',4326);<BR>><BR>><BR>> 1) Are you using inherited tables and having a different table for each<BR>> user? I'm assuming that is why you are doing<BR>><BR>> UPDATE ONLY<BR>><BR>> rather than UPDATE (UPDATE ONLY really only makes sense if you are<BR>> using inherited tables and don't want the update to cascade to child<BR>> tables, but then if its inherited - its still silly since you are just<BR>> updating child tables anyway)<BR>><BR>><BR>> 2) Where is that $user coming from. Make sure you know where that $user<BR>> is coming from and you properly sanitize it otherwise its a prime target<BR>> for SQL Injection attack.<BR>><BR>> 3) How big is your user_polygon table? Speed would depend on that<BR>><BR>> - this particular statement you have is inefficient - because it is<BR>> either doing a correlated subselect for every record or its updating<BR>> every record to the same value. Since you are not prefixing things - its<BR>> hard for me to tell what exactly this is doing. This I suppose would<BR>> make sense if you are creating a different table for each user<BR>><BR>> UPDATE ONLY user_polygon SET numreg = (SELECT COUNT (code) FROM<BR>> user_points<BR>> WHERE name = polygon_code);<BR>><BR>> but its generally more efficient to write a sub select<BR>><BR>> UPDATE user_polygon SET numreg = u.cnt<BR>> FROM (SELECT COUNT(up.code) as cnt, up.name<BR>> FROM user_point up<BR>> GROUP BY up.name) u<BR>> WHERE u.name = user_polygon.polygon_code;<BR>><BR>><BR>> 3) I have no idea what the point of the first bounding box insert you<BR>> have is for<BR>> since ST_intersects, ST_Contains already have bounding box checks in<BR>> them. So I suspect this step you have is is not necessary<BR>><BR>> For this part<BR>><BR>> SELECT<BR>> distinct(polygon_code),the_geom FROM polygon where polygon."."the_geom<BR>> &&<BR>> GeometryFromText('".$row['astext']."',4326)<BR>><BR>><BR>> 4) Again this statement<BR>> UPDATE ONLY user_polygon SET userid ='$user';<BR>><BR>> Is updating every record in user_polygon to the current user. So if you<BR>> are looping thru a user set which I can only guess you are, this<BR>> statement is overwritting the previous updates.<BR>><BR>> If again you have one table per user, then this is fine - but its<BR>> cheaper to do everything in your insert rather than doing an update<BR>> after the fact. Updates tend to be slower than inserts.<BR>><BR>> Hope that helps a bit,<BR>> Regina<BR>><BR>><BR>><BR>><BR>> <BR>><BR>> -----Original Message-----<BR>> From: postgis-users-bounces@postgis.refractions.net<BR>> [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of pere<BR>> roca<BR>> Sent: Monday, June 30, 2008 4:25 AM<BR>> To: postgis-users@postgis.refractions.net<BR>> Subject: [postgis-users] point-in-polygon SQL sentence performance<BR>><BR>><BR>> hi!<BR>><BR>> I wanna make a point-in-polygon operation but takes looong time (about 4<BR>> minutes!) and the CPU consuming of the server<BR>> becomes huge when performing this operation for 15000 points /<BR>> polygons:<BR>><BR>> I need to count how many user points fall in each polygon and perform<BR>> some<BR>> operations based on that.<BR>> We initially have a point_table that stores multi-user point data. We<BR>> extract from there only this-user-data and create new tables:<BR>> user_points<BR>> and user_polygon. To the first one we will insert the code of the<BR>> polygon in<BR>> which it falls; counting how many many times the same polygon_code is<BR>> repeated, we will calculate number-points_in_polygon...<BR>> The important code is commented below (calling SQL from PHP):<BR>><BR>> //we use the points bbox to narrow the future spatial queries (will only<BR>> select polygons falling inside this bbox; from 1991 initial polygons,<BR>> only<BR>> 85 are from now on used); it is useful when points are close each other<BR>> (if<BR>> there is an outlayer then bbox is not useful...)<BR>><BR>> $extent="select astext(extent(points_table.the_geom)) from points_table<BR>> where userid='$user'";<BR>> $extent_result=pg_query($extent);<BR>><BR>> while ($row = pg_fetch_array($extent_result, NULL, PGSQL_ASSOC))<BR>> {<BR>> //creates a NEW polygon table that will store only polygon code and<BR>> polygon<BR>> geometry that fall INSIDE the point's bbox<BR>> $poligon_sql2="INSERT INTO user_polygon (polygon_code,the_geom)<BR>> (SELECT<BR>> distinct(polygon_code),the_geom FROM polygon where polygon."."the_geom<BR>> &&<BR>> GeometryFromText('".$row['astext']."',4326));<BR>> UPDATE ONLY user_polygon SET userid ='$user';<BR>><BR>> //we previously created a new user point table, where we insert some<BR>> info.<BR>> The table is created because we are taking data from a multi-user table<BR>> and<BR>> now we want a single user-->a single point table<BR>><BR>> INSERT INTO user_points (genus, species, name, code, usuario, the_geom)<BR>> (SELECT genus, specie, codigo, oid, userid, the_geom FROM points WHERE<BR>> userid = '$user');<BR>><BR>> //in this user POINT table we set the code from the polygon's table<BR>> where<BR>> the point falls inside;<BR>> //I expected this one to be the "big" operation , but it's not (takes<BR>> "only"<BR>> 1 min 18 sec)<BR>><BR>> UPDATE ONLY user_points SET code = (SELECT polygon_code FROM<BR>> user_polygon<BR>> WHERE user_polygon.the_geom && user_points.the_geom AND CONTAINS<BR>> (user_polygon.the_geom,user_points.the_geom));<BR>><BR>> //updating user polygon table, counting how many times do we have a<BR>> polygon<BR>> code in our POINT table --> number of points in each polygon<BR>> // this updating operation takes 1 min 20 seconds!<BR>><BR>> UPDATE ONLY user_polygon SET numreg = (SELECT COUNT (code) FROM<BR>> user_points<BR>> WHERE name = polygon_code);<BR>><BR>> // this updating operation also takes about 1 min 20 seconds!<BR>><BR>> UPDATE ONLY user_polygon SET numtax = (SELECT COUNT (genus) FROM (SELECT<BR>> DISTINCT (genus) FROM user_points WHERE code = polygon_code) AS foo);<BR>><BR>> All important data is indexed; Any ideas to get better performance?<BR>><BR>> thanks!<BR>> <BR>> Pere Roca<BR>> EDIT project (<A href="http://edit.csic.es/edit_geo/prototype/edit.html">http://edit.csic.es/edit_geo/prototype/edit.html</A>)<BR>><BR>><BR>> --<BR>> View this message in context:<BR>> <A href="http://www.nabble.com/point-in-polygon-SQL-sentence-performance-tp181908">http://www.nabble.com/point-in-polygon-SQL-sentence-performance-tp181908</A><BR>> 54p18190854.html<BR>> Sent from the PostGIS - User mailing list archive at Nabble.com.<BR>><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>> -----------------------------------------<BR>> The substance of this message, including any attachments, may be<BR>> confidential, legally privileged and/or exempt from disclosure<BR>> pursuant to Massachusetts law. It is intended<BR>> solely for the addressee. If you received this in error, please<BR>> contact the sender and delete the material from any computer.<BR>><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR><BR>--<BR>View this message in context: <A href="http://www.nabble.com/point-in-polygon-SQL-sentence-performance-tp18190854p18199519.html">http://www.nabble.com/point-in-polygon-SQL-sentence-performance-tp18190854p18199519.html</A><BR>Sent from the PostGIS - User mailing list archive at Nabble.com.<BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>