[postgis-users] Measure distance to nearest border

[Andreas Forø Tollefsen]

Ok. I will try to explain more in detail.
I have a vector grid consisting of 64818 grid cells. Each of these cells are
stored in the pgfinal2008 table, with two geometries variables: centroid
(point) and cell (polygon).
Each grid cell have a gwcode variable which is the country code.

In addition i have a table with all countries represented by polygons, and a
table with all countries represented by their boundaries (line).

What i want to do is to measure the distance from each cell to the closest
border..

While a simple measure of ST_Distance(pgfinal2008.centroid,
cshapes.the_geom) WHERE pgfinal2008.gwcode != cshapes.gwcode will measure
the distances, it will measure to all the polygons where the gwcode is
different.
However, I can apply a MIN() function to select the nearest of these, though
this will take considerable time since every cell need to be measured to
every country.

What i need to solve is how to limit the distance to measuring only to
neighbouring countries of the country the cell is located.

Thanks for your help Nicklas.



2010/10/21 Nicklas Avén <nicklas.aven at jordogskog.no>

>  hmm, I have to admit I don't really understand what you are trying to do,
> but there are some thingsI don't think you really mean.
>
> select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM
> pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear
> <= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;
>
> Why do you have, cshapes c, there. if you have 200 countries in it taking
> away that will reduce the querytime to 1/200
> What you get here is theclosest country from your first grid cell.
>
>  SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance)
> FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid
> GROUP BY pgfinal2008.cell, borddisttest2.gid;
>  This I don't get. Do you have many rows in pgfinal2008 with the same
> value in pgfinal2008.cell but different pgfinal2008.gid. What is "cell"?
>
> /Nicklas
>
>
> 2010-10-21 Andreas Forø Tollefsen wrote:
>
> Thanks.>
> I have found a way to do this calculation.
> >
> However, since i gave 64818 cells and almost 200 countries, the distance
> from every cell to every country is calculated before the minimum distance
> is selected.
> >
> This takes awful lot of time.
> >
>
> >
> >
> Any ideas on how to increase the performance?
> >
>
> >
> >
> >
> DROP TABLE IF EXISTS borddisttest2;
> >
> CREATE TABLE borddisttest2 AS
> >
> select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM
> pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear
> <= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;
> >
>
> >
> >
> DROP TABLE IF EXISTS borddist2008;
> >
> CREATE TABLE borddist2008 AS
> >
> SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance)
> FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid
> >
> GROUP BY pgfinal2008.cell, borddisttest2.gid;
>
> >>
> 2010/10/13 Nicklas Avén <nicklas.aven at jordogskog.no>
> > >
> >
> Hi
> >
>
> >
> You can not cast to geography that way (if I haven't missed something
> essential)
> > And if you could you would mix geometry and geograph type in ST_Distance,
> which wouldn't work.
> >
>
> >
> St_Distance for geometry vs geography uses totally differnt algorithms. The
> geography calculations is far more advanced.
> >
>
> >
> ST_Shortestline only works for geometry type.
> >
>
> >
> /Nicklas
> >
> >
>
> >
> >
>
> >
>
> > 2010-10-13 Andreas Forø Tollefsen wrote:
> >
> > Hi>
> >
>
> > >
> > >
> Thanks,
> > >
> I will give it a try.
> > >
> The reason I used the geography types in the distance query, were to get
> the distance in meters instead of degrees.
> > >
>
> > >
> >
> >
> Andreas
> > >
> > >> >
> >
> >
>
> >
> 2010/10/13 Nicklas Avén <nicklas.aven at jordogskog.no>
> > > > >
> > >
> Hi
> > >
>
> > >
> As I understand you you want to get the distance from each cell to the
> closest neighbour country. Then, why don't you just query the distance from
> your cell to closest country with other gwcode?
> > >
> Maybe something like:
> > >
>
> > >
> Create table borddisttest as
> > >
> Select ST_Distance(st_collect(b.the_geom), c.centroid)
> > >
> from cshapes a, cshapes b, pgfinal2008 c
> > >
> where a.gwcode=c.gwcode and b.gwcode != c.gwcode and
> st_intersects(a.the_geom, b.the_geom)
> > >
> group by c.gwcode and c.the_geom;
> > >
> > >
> With some tweaking like ordering the cells by gwcode I don't think the
> intersection calculation have to be done for each cell. Otherwise this is a
> very bad approach if there are many grid-cells.
> > >
>
> > >
> indexing on gwcode on both tables and spatial index on the country geoms
> for the intersection will also be of importance.
> > >
>
> > >
> I don't understand:
> > >
> geography(pgfinal2008.centroid)
> > >
>
> > >
> what does geography means here?
> > >
>
> > >
> I would also go for calculating the centroids on the fly. It should be fast
> and you will not get the trouble of updating the centroid column if
> adjustuing the grid.
> > >
>
> > >
> About doing calculations comparing geometries inside a dataset you use self
> join.
> > >
>
> > >
> You can join a table with itself as long as you put an alias so you can
> identify them like I did above with a, b and c
> > >
>
> > >
> Well, I am not sure I answered the right question but anyway...
> > >
> HTH
> > >
> Nicklas
> > >
> > >
>
> > >
> > >
>
> > > 2010-10-13 Andreas Forø Tollefsen wrote:
> > >
> > > >
> > >
> Hi all,
> > > >
>
> > > >
> > > >
> I have two datasets. On is a quadrate grid, and the other is the boundaries
> of all countries.
> > > >
> What i want to do is to measure the distance from each centroid of the grid
> cells to the nearest border, but not all borders. Only the international.
> > > >
>
> > > >
> > > >
> First i convert my country polygon dataset into line features:
> > > >
> > > >
> select gwcode, gwsyear, gweyear, ST_Boundary(the_geom) AS line Into
> cshapes_line from cshapes;
> > > >
>
> > > >
> > > >
> Next i calculate the distance from the centroid of each cell to the nearest
> border where the gwcode (country code) of the cell is the same as the gwcode
> in the line feature.
> > > >
> drop table if exists borddisttest;
> > > >
> select pgfinal2008.cell, pgfinal2008.gwcode,
> ST_ShortestLine(pgfinal2008.centroid, st_boundary(cshapes.the_geom))
> > > >
> AS shortestline, ST_Distance(geography(pgfinal2008.centroid),
> st_boundary(cshapes.the_geom))/1000 AS borddist
> > > >
> into borddisttest
> > > >
> from cshapes, pgfinal2008
> > > >
> where cshapes.gwcode = pgfinal2008.gwcode;
> > > >
>
> > > >
> > > >
> The problem here is that when using ST_Boundary, it converts all boundaries
> into lines, while i only want to get the boundaries where two polygons with
> different gwcodes meet.
> > > >
> The lines where two countries meet are overlapping, meaning these borders
> have two gwcodes but in different line entries.
> > > >
>
> > > >
> > > >
> Is there a way to calculate the overlaps within one dataset? If i could do
> this, then i could put a clause so the measurement only measures to lines
> with 2 or more gwcodes.
> > > >
>
> > > >
> > > >
> Thanks.
>
> > >_______________________________________________
> > > postgis-users mailing list
> > >postgis-users at postgis.refractions.net
> >
> >
> >postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
>
> > >
>
> >_______________________________________________
> > postgis-users mailing list
> >postgis-users at postgis.refractions.net
> >postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101021/b71e6339/attachment.html>