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] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
