<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18975"></meta>
</head>
<body>
<div align="left">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.</div>
<div align="left"> </div>
<div align="left">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;</div>
<div align="left"><br />
Why do you have, cshapes c, there. if you have 200 countries in it taking away that will reduce the querytime to 1/200</div>
<div align="left">What you get here is theclosest country from your first grid cell.</div>
<div align="left"> </div>
<div align="left">
<div>SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid</div>GROUP BY pgfinal2008.cell, borddisttest2.gid;<br />
</div>
<div align="left">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"? </div>
<div align="left"> </div>
<div align="left">/Nicklas</div>
<div align="left"> </div>
<div align="left"><br />
2010-10-21 Andreas Forø Tollefsen wrote:<br />
<br />
Thanks.></div>
<div>I have found a way to do this calculation.</div>>
<div>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.</div>>
<div>This takes awful lot of time.</div>>
<div><br />
></div>>
<div>Any ideas on how to increase the performance?</div>>
<div><br />
></div>>
<div>>
<div>DROP TABLE IF EXISTS borddisttest2;</div>>
<div>CREATE TABLE borddisttest2 AS</div>>
<div>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;</div>>
<div><br />
></div>>
<div>DROP TABLE IF EXISTS borddist2008;</div>>
<div>CREATE TABLE borddist2008 AS</div>>
<div>SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid</div>>
<div>GROUP BY pgfinal2008.cell, borddisttest2.gid;</div><br />
>>
<div>2010/10/13 Nicklas Avén <<a>nicklas.aven@jordogskog.no</a>><br />
> >
<div>>
<div align="left">Hi</div>>
<div align="left"> </div>>
<div align="left">You can not cast to geography that way (if I haven't missed something essential)<br />
> And if you could you would mix geometry and geograph type in ST_Distance, which wouldn't work.</div>>
<div align="left"> </div>>
<div align="left">St_Distance for geometry vs geography uses totally differnt algorithms. The geography calculations is far more advanced. </div>>
<div align="left"> </div>>
<div align="left">ST_Shortestline only works for geometry type.</div>>
<div align="left"> </div>>
<div align="left">/Nicklas</div>>
<div>>
<div> </div>>
<div>>
<div align="left"> </div>>
<div align="left"><br />
> 2010-10-13 Andreas Forø Tollefsen wrote:<br />
><br />
> Hi></div>>
<div><br />
> ></div>> >
<div>Thanks,</div>> >
<div>I will give it a try.</div>> >
<div>The reason I used the geography types in the distance query, were to get the distance in meters instead of degrees.</div>> >
<div><br />
> ></div>> </div></div>>
<div>Andreas<br />
> ><br />
> >> >
<div>>
<div>>
<div> </div>>
<div>2010/10/13 Nicklas Avén <<a>nicklas.aven@jordogskog.no</a>><br />
> > > >
<div>> >
<div align="left">Hi</div>> >
<div align="left"> </div>> >
<div align="left">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?</div>> >
<div align="left">Maybe something like:</div>> >
<div align="left"> </div>> >
<div align="left">Create table borddisttest as</div>> >
<div align="left">Select ST_Distance(st_collect(b.the_geom), c.centroid)</div>> >
<div align="left">from cshapes a, cshapes b, pgfinal2008 c</div>> >
<div align="left">where a.gwcode=c.gwcode and b.gwcode != c.gwcode and st_intersects(a.the_geom, b.the_geom)</div>> >
<div align="left">group by c.gwcode and c.the_geom;<br />
> ></div>> >
<div align="left">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.</div>> >
<div align="left"> </div>> >
<div align="left">indexing on gwcode on both tables and spatial index on the country geoms for the intersection will also be of importance.</div>> >
<div align="left"> </div>> >
<div align="left">I don't understand:</div>> >
<div align="left">geography(pgfinal2008.centroid)</div>> >
<div align="left"> </div>> >
<div align="left">what does geography means here?</div>> >
<div align="left"> </div>> >
<div align="left">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.</div>> >
<div align="left"> </div>> >
<div align="left">About doing calculations comparing geometries inside a dataset you use self join. </div>> >
<div align="left"> </div>> >
<div align="left">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</div>> >
<div align="left"> </div>> >
<div align="left">Well, I am not sure I answered the right question but anyway...</div>> >
<div align="left">HTH</div>> >
<div align="left">Nicklas</div>> >
<div>> >
<div> </div>> >
<div>> >
<div align="left"><br />
> > 2010-10-13 Andreas Forø Tollefsen wrote:<br />
> ><br />
> > ></div>> >
<div>Hi all,</div>> > >
<div><br />
> > ></div>> > >
<div>I have two datasets. On is a quadrate grid, and the other is the boundaries of all countries.</div>> > >
<div>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.</div>> > >
<div><br />
> > ></div>> > >
<div>First i convert my country polygon dataset into line features:</div>> > >
<div>> > >
<div>select gwcode, gwsyear, gweyear, ST_Boundary(the_geom) AS line Into cshapes_line from cshapes;</div>> > >
<div><br />
> > ></div>> > >
<div>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.</div>> > >
<div>drop table if exists borddisttest;</div>> > >
<div>select pgfinal2008.cell, pgfinal2008.gwcode, ST_ShortestLine(pgfinal2008.centroid, st_boundary(cshapes.the_geom)) </div>> > >
<div>AS shortestline, ST_Distance(geography(pgfinal2008.centroid), st_boundary(cshapes.the_geom))/1000 AS borddist </div>> > >
<div>into borddisttest </div>> > >
<div>from cshapes, pgfinal2008 </div>> > >
<div>where cshapes.gwcode = pgfinal2008.gwcode;</div></div>> > >
<div><br />
> > ></div>> > >
<div>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.</div>> > >
<div>The lines where two countries meet are overlapping, meaning these borders have two gwcodes but in different line entries.</div>> > >
<div><br />
> > ></div>> > >
<div>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.</div>> > >
<div><br />
> > ></div>> > >
<div>Thanks.</div></div></div></div><br />
> >_______________________________________________<br />
> > postgis-users mailing list<br />
> ><a>postgis-users@postgis.refractions.net</a><br />
></div></div>>
<div>><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">postgis.refractions.net/mailman/listinfo/postgis-users</a><br />
> ><br />
> ></div></div><br />
> ></div></div><br />
>_______________________________________________<br />
> postgis-users mailing list<br />
><a>postgis-users@postgis.refractions.net</a><br />
><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">postgis.refractions.net/mailman/listinfo/postgis-users</a><br />
><br />
></div><br />
></div>
</body>
</html>