Thanks. Solved! That worked like a charm. Exactly what i wanted. Andreas
2010/10/21 Nicklas Avén <[email protected]> > Ok > > still don't get the second query. If I understand you right you could try > this: > > 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 > > > 2010-10-21 Andreas Forø Tollefsen wrote: > > 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 <[email protected]> > > > > > > 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 <[email protected]> > > > > > > > > > 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 <[email protected]> > > > > > > > > > > > > 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 > > > >[email protected] > > > > > > > >postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > > > > > > > > > > > >_______________________________________________ > > > postgis-users mailing list > > >[email protected] > > >postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > > > > > > > >_______________________________________________ > > postgis-users mailing list > >[email protected] > >postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
