Re: [postgis-users] SQL syntax question
Dear Remi, Many thanks for your reply - makes it much more clear. I actually managed to solve the issue by a table join following the sql query. Thanks again /Manuel On Mon, Mar 9, 2015 at 11:03 PM, Rémi Cura remi.c...@gmail.com wrote: Hey, this is more a postgres/SQL question. Anyway SELECT a.gid, b.gid, st_shortestline(a.geom,b.geom), ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b WHERE ST_DWithin(a.geom,b.geom,2000) = TRUE The syntax is : SELECT choose what you see as final result SELECT * -- : all possible column/attributes SELECT column1, column2, ... --list of column or expression/function FROM choose what table you use and how FROM --one table, or several table using join WHERE choose a way to filter the result ot keep only a part of the result. WHERE --condition to aply to row, only row respecting conditions are kept in the result. This querry means in human langage: For all rows (1,n) of the table *a*, take all row (1,n) of table *b*. (so this give you the row : 1,1 1,2 , ... 1,n 2,1 2,2 ... 2,n .. n,1 n,2 ... n,n) For this pairs of rows, keep only those that are spatially close enough (2 km) Then, for remaining pair of rows get me the column gid from a and b, the shortest line from a and b, the (min) distance from a to b Now what you askis to have the distance between all pairs, thus you must remove the filtering condition SELECT a.gid, b.gid, ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b As for your id, your sentence is totally unclear to me. Cheers, Rémi-C 2015-03-08 22:52 GMT+01:00 Manuel Kohout manu.koh...@gmail.com: Hi, I have found following SQL query for measuring distances between polygon edges: select a.gid from_gid, b.gid to_gid, st_shortestline(a.geom,b.geom), st_distance(a.geom,b.geom) as distance from test.nfanwood as a, (select * from test.nfanwood) as b where st_dwithin(a.geom,b.geom,2000) https://duncanjg.wordpress.com/2012/09/23/edge-to-edge-distance-using-postgis/ I am a newbie to GIS and wonder, if you could explain the syntax of the query to me? It works fine, when I run it with my own polygon shapefile: I receive a table with 3 columns a-gid, b.gid and distance, containg rows with all distances of all a.gid to all b.gid. However, what I'd like is to add the distance from a.gid 1 to b.gid 2, a.gid 1 to b.gid 3, ... to my existing attribute table where my 103 patches have ID name Island m001 - m103. Thus all 102 distances of island m001 to all the other 102 islands should be added as rows with ID name m001. Is this possible? /Manu ___ 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 ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Quadgrid Recursive SQL Function - Now Available on Github
Hi everyone I’ve been talking about sharing this code for some time - well here it is. You can find the code on GitHub. Here’s the write up http://dimensionaledge.com/quadgrid-generation-using-recursive-sql-function/ The blog post includes a GIF animation that shows the recursive quadgrid in action, plus a 3D representation of Sydney’s population density. Quadgrids are not limited to underlying points. You can use any value criteria, such as the length of line strings, if for say quadgridding a set of watercourses or roads. Best regards Mark ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Much Faster Point in Polygon Count using KEYWORD LATERAL
Hi All Got zero response to my question below, but the following LATERAL QUERY PATTERN is achieving a 75% reduction in query times for big point in polygon counts. This seems pretty cool - again, has anyone else come across anything like this? I’m keen to hear other people’s experiences. The results seem too good to ignore. BACKGROUND Table has 21 million points, with a GIST index. The task is to count points within 4 quadcells. In total 1.86 Million points intersect the quadcells. TRADITIONAL QUERY PATTERN - OLD WAY SELECT l.the_geom, count(p.pid) FROM (SELECT DE_MakeRegularQuadCells(wkb_geometry) as the_geom FROM abs_aus11_tiles_32k WHERE tid IN (17864)) l, tutorials.abs_mb11_points p WHERE ST_Intersects(l.the_geom, p.wkb_geometry) AND l.the_geom p.wkb_geometry GROUP BY l.the_geom 11.7 seconds - first time (reboot, nothing in cache) 11.2 seconds - second time (post-cache) LATERAL QUERY PATTERN - NEW WAY SELECT l.the_geom, r.pcount FROM (SELECT DE_MakeRegularQuadCells(wkb_geometry) as the_geom FROM abs_aus11_tiles_32k WHERE tid IN (17864)) l, LATERAL (SELECT l.the_geom, count(*) as pcount FROM tutorials.abs_mb11_points WHERE ST_Intersects(l.the_geom, wkb_geometry) AND l.the_geom wkb_geometry) r; 3.1 seconds - first time (reboot, nothing in cache) 2.4 seconds - second time (post-cache) On 8 Mar 2015, at 12:28 pm, Mark Wynter m...@dimensionaledge.com wrote: Hi All To provide some context, I’m in the midst of refactoring my recursive quadgrid function which I’ll share with everyone in the next few days. I’ve got a table with many millions of points and I so I’ve been playing with different query patterns to find the fastest way possible of counting points within a set of quadcells. The way I’ve done this traditionally is to put a GIST index on the points table, and then use a CROSS JOIN query of the form: SELECT l.the_geom, count(p.pid) FROM (SELECT DE_MakeRegularQuadCells(wkb_geometry) as the_geom FROM abs_aus11_tiles_32k WHERE tid IN (17865)) l, tutorials.abs_mb11_points p WHERE ST_Intersects(l.the_geom, p.wkb_geometry) AND l.the_geom p.wkb_geometry GROUP BY 1; “0103000...;688154 “0103000...;473202 “0103000...;84516 About 4.4 seconds for sum 1.2 million points (not startling). Plus there’s a grid cell with zero points, hence this geometry doesn’t get returned by this query pattern. Bummer. When I look at the query plan, the GROUP BY clause seems to be acting as a significant handbrake on the query performance. I’ve also looked at a LEFT JOIN instead of a CROSS JOIN to solve the problem of the non-returning quad cell, but I understand GIST indexes are ineffectual on LEFT JOINS. A faster way that I’ve found is to incorporate the KEYWORD ‘LATERAL’ into the join. This allows me to reference the left table in the right table: SELECT l.the_geom, r.pcount FROM (SELECT DE_MakeRegularQuadCells(wkb_geometry) as the_geom FROM abs_aus11_tiles_32k WHERE tid IN (17865)) l, LATERAL (SELECT count(*) as pcount, l.the_geom FROM tutorials.abs_mb11_points WHERE ST_Intersects(l.the_geom, wkb_geometry) AND l.the_geom wkb_geometry) r; 0103000…; 473202 0103000…; 84516 0103000…”; 0 0103000…; 688154 This cuts the query time down to 1.7 seconds, mostly be avoiding the need to use GROUP BY, and all quadcells are returned even those with zero points in polygons. In effect the query appears to be mimicking a LEFT JOIN. I was expecting that the LATERAL CROSS JOIN would have produced the cartesian product of the two tables, and that I would have to enforce a WHERE condition ST_Equals(l.the_geom, r.the_geom);. Alas not. Just wondering if anyone else has encountered this when using LATERAL, and could shed some light on why this is the case? ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Line of sight (LOS) Calculation
Il 09/03/2015 04:16, Mark Wynter ha scritto: An example of how you can generate Viewsheds in PostGIS via PL/R and Grass using a single SQL query statement. CREATE OR REPLACE FUNCTION generate_viewshed(coordxy text) RETURNS text as Hi all. Please note that in GRASS7, recently released, r.los has been replaced by the (much more efficient) r.viewshed. All the best. -- Paolo Cavallini - www.faunalia.eu QGIS PostGIS courses: http://www.faunalia.eu/training.html ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] SQL syntax question
Hey, this is more a postgres/SQL question. Anyway SELECT a.gid, b.gid, st_shortestline(a.geom,b.geom), ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b WHERE ST_DWithin(a.geom,b.geom,2000) = TRUE The syntax is : SELECT choose what you see as final result SELECT * -- : all possible column/attributes SELECT column1, column2, ... --list of column or expression/function FROM choose what table you use and how FROM --one table, or several table using join WHERE choose a way to filter the result ot keep only a part of the result. WHERE --condition to aply to row, only row respecting conditions are kept in the result. This querry means in human langage: For all rows (1,n) of the table *a*, take all row (1,n) of table *b*. (so this give you the row : 1,1 1,2 , ... 1,n 2,1 2,2 ... 2,n .. n,1 n,2 ... n,n) For this pairs of rows, keep only those that are spatially close enough (2 km) Then, for remaining pair of rows get me the column gid from a and b, the shortest line from a and b, the (min) distance from a to b Now what you askis to have the distance between all pairs, thus you must remove the filtering condition SELECT a.gid, b.gid, ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b As for your id, your sentence is totally unclear to me. Cheers, Rémi-C 2015-03-08 22:52 GMT+01:00 Manuel Kohout manu.koh...@gmail.com: Hi, I have found following SQL query for measuring distances between polygon edges: select a.gid from_gid, b.gid to_gid, st_shortestline(a.geom,b.geom), st_distance(a.geom,b.geom) as distance from test.nfanwood as a, (select * from test.nfanwood) as b where st_dwithin(a.geom,b.geom,2000) https://duncanjg.wordpress.com/2012/09/23/edge-to-edge-distance-using-postgis/ I am a newbie to GIS and wonder, if you could explain the syntax of the query to me? It works fine, when I run it with my own polygon shapefile: I receive a table with 3 columns a-gid, b.gid and distance, containg rows with all distances of all a.gid to all b.gid. However, what I'd like is to add the distance from a.gid 1 to b.gid 2, a.gid 1 to b.gid 3, ... to my existing attribute table where my 103 patches have ID name Island m001 - m103. Thus all 102 distances of island m001 to all the other 102 islands should be added as rows with ID name m001. Is this possible? /Manu ___ 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] Line of sight (LOS) Calculation
Very cool solution ! Could you give a better estimate of run time? (example of DEM resolution, time?) Cheers, Rémi-C 2015-03-09 9:45 GMT+01:00 Paolo Cavallini cavall...@faunalia.it: Il 09/03/2015 04:16, Mark Wynter ha scritto: An example of how you can generate Viewsheds in PostGIS via PL/R and Grass using a single SQL query statement. CREATE OR REPLACE FUNCTION generate_viewshed(coordxy text) RETURNS text as Hi all. Please note that in GRASS7, recently released, r.los has been replaced by the (much more efficient) r.viewshed. All the best. -- Paolo Cavallini - www.faunalia.eu QGIS PostGIS courses: http://www.faunalia.eu/training.html ___ 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
