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 asSelect ST_Distance(st_collect(b.the_geom), c.centroid)from
cshapes a, cshapes b, pgfinal2008 cwhere 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...HTHNicklas
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
