Thanks. Solved!
That worked like a charm.
Exactly what i wanted.
Post by Nicklas AvénOk
still don't get the second query. If I understand you right you could try
CREATE TABLE borddisttest2 AS
select a.gid, a.cell, 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 and a.gwcode = c.gwcode and
st_intersects(b.the_geom, c.the_geom)
GROUP BY a.gid LIMIT 1;
Here I assume that every country only have one representation in cshape.
What I do is that I try to only get the countries that intersects with the
country that has the same gwcode as the cell you are calculating.
Here it is essential to have spatial index on the country-polygons and the
centriod-point
You should also have index on gwsyear, gweyear and gwcode in both
pgfinal2008 and cshapes.
I guess your limit 1 is just for testing purposes
Probably you could make it quite a lot faster if you could trick it to inly
do the intersection process once per country and not once per cell.
Right now I don't see any good way to do that. Hopefully someone else sees
a solution to that, or I will return if it comes to me.
But maybe, if I recall right there were some discussions here that
postgresql can cache results from one row to another. If soo it should work
by itself or at least if you order your query by the gwcode so it takes all
grid cells from one country first.
/Nicklas
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.
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;
Post by Nicklas AvénWhy 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
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;
Hi
You can not cast to geography that way (if I haven't missed something
essential)
Post by Nicklas AvénPost by Nicklas AvénAnd 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
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
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?
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.
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
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.
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.
Post by Nicklas AvénPost by Nicklas AvénPost by Nicklas Avén_______________________________________________
postgis-users mailing list
postgis.refractions.net/mailman/listinfo/postgis-users
Post by Nicklas Avén_______________________________________________
postgis-users mailing list
postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
http://postgis.refractions.net/mailman/listinfo/postgis-users