Re: [postgis-users] - The greater length River inside one Watershed
hello marcello. what is your data model? dependng on the data model (if you have a column, lets say, rivername) you can use a group by clause and st_collect on your geometry field. ex: select rivername, max(st_collect(the_geom)) from rivers group by rivername the intersects clause its up to you , depending on your néds. not tested, but should work. On 4/26/11, Marcello Benigno benigno.marce...@gmail.com wrote: Hi All, I need a query that returns the greater length of river within a watershed. Does anyone have any suggestions on how I can solve this problem? The rivers are formed by segments. if each were a continuous line would be easy. Here is a print-screen of the problem, in yellow is what would be the result of the query. Thanks in advance, -- *Marcello Benigno B. de Barros Filho* Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE http://profmarcello.blogspot.com http://about.me/marcello.benigno -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] - The greater length River inside one Watershed
ooops, prior to max use st_length! On 4/26/11, George Silva georger.si...@gmail.com wrote: hello marcello. what is your data model? dependng on the data model (if you have a column, lets say, rivername) you can use a group by clause and st_collect on your geometry field. ex: select rivername, max(st_collect(the_geom)) from rivers group by rivername the intersects clause its up to you , depending on your néds. not tested, but should work. On 4/26/11, Marcello Benigno benigno.marce...@gmail.com wrote: Hi All, I need a query that returns the greater length of river within a watershed. Does anyone have any suggestions on how I can solve this problem? The rivers are formed by segments. if each were a continuous line would be easy. Here is a print-screen of the problem, in yellow is what would be the result of the query. Thanks in advance, -- *Marcello Benigno B. de Barros Filho* Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE http://profmarcello.blogspot.com http://about.me/marcello.benigno -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] - The greater length River inside one Watershed
Probably you can look at this (pgrouting) http://www.pgrouting.org/ http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/ or as I see you use qgis you also can try this qgis plugin http://www.youtube.com/watch?v=T2JTuQOBXz0 http://gis-lab.info/qa/road-graph-eng.html Just adapt for your needs. However it depends what kind and how many data you have. 2011/4/26 George Silva georger.si...@gmail.com ooops, prior to max use st_length! On 4/26/11, George Silva georger.si...@gmail.com wrote: hello marcello. what is your data model? dependng on the data model (if you have a column, lets say, rivername) you can use a group by clause and st_collect on your geometry field. ex: select rivername, max(st_collect(the_geom)) from rivers group by rivername the intersects clause its up to you , depending on your néds. not tested, but should work. On 4/26/11, Marcello Benigno benigno.marce...@gmail.com wrote: Hi All, I need a query that returns the greater length of river within a watershed. Does anyone have any suggestions on how I can solve this problem? The rivers are formed by segments. if each were a continuous line would be easy. Here is a print-screen of the problem, in yellow is what would be the result of the query. Thanks in advance, -- *Marcello Benigno B. de Barros Filho* Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE http://profmarcello.blogspot.com http://about.me/marcello.benigno -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ 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] Need help with a query to assign attributes to lines the intersect multiple polygons
Hi Brigit, Thanks! I'm not sure I would have thought of using DISTINCT ON, but that seems to work. It seems Postgresql picks the LAST item when using DISTINCT ON instead if the FIRST row that matches, so I had to change DESC to ASC. I'm not sure hold stable that will be because it assumes some side effect of how DISTINCT ON works, which probably is not defined in the SQL standard (but I have not looked). But for now it looks like it will work and should be faster than using the stored procedure. And it gives me a new way to think about this class of tagging problems. Thanks again, this helps a lot! -Steve On 4/26/2011 4:51 AM, Birgit Laggner wrote: Hi Steve, I thought about your problem and I would solve it with distinct on - like this: update line set poly_att=sel.poly_att from (select distinct on (a.line_id) a.line_id, b.poly_att from line a inner join polygons b on a.the_geom b.the_geom where st_intersects(a.the_geom,b.the_geom) order by a.line_id, st_length(st_intersection(a.the_geom, b.the_geom)) desc) sel where line.line_id=sel.line_id; Hope that helps, Birgit. Am 25.04.2011 16:24, schrieb Stephen Woodbridge: Hi all, I have a table a line and a table of polygons. I would like to come up with a query that allow me to link the lines to the polygon where most of the length of the line falls into that polygon. The problem is that the lines may overlap or touch more than one polygon. So I can do this in a stored procedure where I iterate through the lines and intersect them with the overlapping polygons and sort them based on the length of the intersection fragments and limit 1 on the results. It just seems like this should be able to be done using join and sub-query of some kind, but I can't quite figure it out. Ultimately, I want to do an UPDATE line set attr1=b.someattr ... Thoughts, -Steve ___ 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
[postgis-users] GeomFromText in a user function.
I'm going to apologize up front for being a nood.
I have been trying to create a user function that I can call as needed
by passing in bbox coordinates. I have the standard SQL working as show
below;
SELECT *
FROM MyTable t
WHERE WITHIN(
GeomFromText('POLYGON((5 1, 8 4, 7 3, 6 2, 5 1))'),t.geom);
The above runs fine, they are not valid values based on the geom column
is based on lat/lon.
When I place it in a PostgreSQL function it keeps telling me type
geomfromtext does not exist.
I have run out of ideas and can't find a lot on the net about PostgreSQL
function similar to this. Below is my last attempt:
CREATE OR REPLACE FUNCTION fn_Poly(double precision, double precision,
double precision, double precision, double precision, double precision,
double precision, double precision)
RETURNS SETOF MyTable AS
'SELECT *
FROM MyTable t
WHERE WITHIN(
GeomFromText('' || || POLYGON(('' ||
$5 || '' '' || $1 || '', '' ||
$8 || '' '' || $4 || '', '' ||
$7 || '' '' || $3 || '', '' ||
$6 || '' '' || $2 || '', '' ||
$5 || '' '' || $1 ||
''))'') || ,t.geom)'
LANGUAGE sql;
Any help would be greatly appreciated.
Charlie
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] GeomFromText in a user function.
Charles,
Which version of PostGIS are you using?
If you are using trunk (PostGIS 2.0), those won't work since they were
removed. Use ST_GeomFromText and ST_Within instead. Though I don't think
that is the problem based on your error.
It's hard to read you code. Please use $$ $$ quoting instead of ' that way
you don't have to escape out your inner quotes.
Leo
http://www.postgis.us
_
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Charles
E. Deaton
Sent: Tuesday, April 26, 2011 1:46 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] GeomFromText in a user function.
I'm going to apologize up front for being a nood.
I have been trying to create a user function that I can call as needed by
passing in bbox coordinates. I have the standard SQL working as show below;
SELECT *
FROM MyTable t
WHERE WITHIN(
GeomFromText('POLYGON((5 1, 8 4, 7 3, 6 2, 5 1))'),t.geom);
The above runs fine, they are not valid values based on the geom column is
based on lat/lon.
When I place it in a PostgreSQL function it keeps telling me type
geomfromtext does not exist.
I have run out of ideas and can't find a lot on the net about PostgreSQL
function similar to this. Below is my last attempt:
CREATE OR REPLACE FUNCTION fn_Poly(double precision, double precision,
double precision, double precision, double precision, double precision,
double precision, double precision)
RETURNS SETOF MyTable AS
'SELECT *
FROM MyTable t
WHERE WITHIN(
GeomFromText('' || || POLYGON(('' ||
$5 || '' '' || $1 || '', '' ||
$8 || '' '' || $4 || '', '' ||
$7 || '' '' || $3 || '', '' ||
$6 || '' '' || $2 || '', '' ||
$5 || '' '' || $1 ||
''))'') || ,t.geom)'
LANGUAGE sql;
Any help would be greatly appreciated.
Charlie
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] GeomFromText in a user function.
On 27 April 2011 05:46, Charles E. Deaton cdea...@corp.realcomp.com wrote:
I have been trying to create a user function that I can call as needed by
passing in bbox coordinates.
Are you trying to select items in a box-shaped geometry? There are a
few better ideas to construct a simple box polygon that use fewer than
eight parameters:
-- Three numeric parameters, using a box centre (x,y) and expand
distance of 1:
SELECT ST_AsEWKT(ST_Expand(ST_MakePoint(15,25), 1));
-- One parameter, using the bounding box from a geometry
SELECT ST_AsEWKT(Box2D('POLYGON ((10 40, 30 20, 40 50, 10
40))'::geometry)::geometry);
Your problem is due to a misinterpretation of the messy concatenation.
Try using dollar-quote literals e.g.: $BODY$This text doesn't do
much$BODY$. Postgres thinks you are trying to use a type named
geomfromtext, which is not your intent. I generally try to avoid
constructing geometries into text using string concatenation as they
are subject to fail if one of the parameters is null, and they are
extra overhead (float - string - parse to float) with possible loss
of precision.
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Geog Buffering, Distance, and Line contruction
All, I have been seeing some interesting results using some of the geog functions. First I'll layout what I am attempting to do, my attempted workaround, and the resulting problems. The goal: On a global offshore wind dataset of 41 million points, I am attempting to calculate the distance to the closest shoreline (done), and build a line to that location (trouble). The beginning problem: There are no geog functions for constructing a line in this manner e.g. No ST_ShortestLine(geog, geog) nor are there geog functions to return the closest point e.g. No ST_ClosestPoint. My workaround (I thought…): My workaround was to be quite simple, use ST_Distance(a.geog, b.geog) to get the distance between a point and the closest shoreline, then use the resulting distance in ST_Buffer(geog, returned_dist) to construct a buffered polygon. Lastly, take the buffer and intersect with the the shoreline and return the point of intersection. One limitation with this method is that you cannot specify the number of quad_segs in ST_Buffer(geog), which depending on the buffer/shoreline relationship could potentially produce a near miss…But, I could live with this limitations and could come up with a percentage increase to always produce an intersection. The larger problem: My method failed, and when investigating the failure I found these results. 1. In comparing ST_Distance(geog) and ST_Distance(geom) the resulting distance were different, staggeringly different depending on latitude and orientation. This WAS expected, even if the two functions measure distance to the same point they would return different measurements due to the nature of projections. 2. I tried to determine if constructing a line using ST_ShortestLine(geom) then casting to geog then calculating length would return a similar distance as ST_Distance(geog). It did not, and proves that the geog, geom functions are not measuring distance to the same point (on the shoreline). I'll mention that I expected this as well. 3. When buffering by the distance ST_Distance(geog) returns, using ST_Buffer(geog), you would expect a point of intersection between the shoreline and the buffer. I understand that since ST_Buffer(geog) does not have the quad_segs option it sets up the possibility of a near miss (hit). It seems that ST_Buffer(geog) comes up short at all latitudes and orientations… Please see my testing SQL below. I am completely up for other workarounds and suggestions. Again, the goal is to be able to construct lines that represent the same distance/point of intersect that ST_Distance(geog) uses. Thanks in advance, Anthony INSERT INTO global_wind.buff_tests SELECT buff.gid, buff.country, buff.lat, buff.geom_dist / 1000 as geom_dist, buff.geog_dist / 1000 as geog_dist, buff.geom_line, Geometry(buff.geog_buff) as buffer_geom, Geometry(ST_Intersection(buff.geog_buff, buff.cntry_geog)) as buff_cntry_inter ST_Intersects(buff.geog_buff, buff.cntry_geog) FROM ( SELECT r.gid, r.country, r.lat, r.geom_dist, r.geog_dist, ST_Buffer(r.pt_geog, r.geog_dist) as geog_buff, r.geom_line, r.cntry_geog FROM ( SELECT a.gid, a.country, ST_Y(a.the_geom) as lat, ST_Length(Geography(ST_ShortestLine(a.the_geom, b.the_geom))) as geom_dist, ST_Distance(a.the_geog, b.geog) as geog_dist, ST_ShortestLine(a.the_geom, b.the_geom) as geom_line, a.the_geog as pt_geog, b.geog as cntry_geog FROM global_wind.offshore_wind a, global_wind.coasts_simp b WHERE a.gid = 2200 AND a.eez_id = b.zones ) r ) buff ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
