Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- gis=> select a.osm_id, a."name:en", b.osm_id, b."name:en", round(ST_Area(ST_Intersection(a.way, b.way)::geography)/1000000) as area_km2, ST_AsText(ST_Centroid(ST_Intersection(a.way, b.way))) as center from at_polygon a inner join at_polygon b on a.osm_id != b.osm_id and a.way && b.way where a.admin_level='2' and b.admin_level='2' and a.boundary='administrative' and b.boundary='administrative' and a.osm_id < b.osm_id and b.osm_id < 0 and ST_Intersects(a.way, b.way) order by area_km2 desc limit 20;
- osm_id | name:en | osm_id | name:en | area_km2 | center
- ----------+-------------------------+----------+--------------------------+----------+--------------------------------------------
- -60199 | Ukraine | -60189 | Russia | 44799 | POINT(34.3620300516289 45.2171795055881)
- -1656678 | South Sudan | -192789 | Sudan | 23147 | POINT(26.050412528483 9.48777979190103)
- -307829 | Haiti | -148838 | United States of America | 1746 | POINT(-75.0137486468775 18.4028571650422)
- -286393 | Argentina | -167454 | Chile | 1507 | POINT(-73.2951125519333 -49.4915930163872)
- -1428125 | Canada | -148838 | United States of America | 578 | POINT(-67.1371505877121 44.459808705718)
- -449220 | Taiwan | -270056 | China | 407 | POINT(119.993523485709 26.2081430743007)
- -449220 | Taiwan | -270056 | China | 277 | POINT(118.351311662216 24.4426029867618)
- -2323309 | The Netherlands | -51477 | Germany | 264 | POINT(6.71178441000846 53.5108114523677)
- -1428125 | Canada | -148838 | United States of America | 109 | POINT(-140.919706509331 69.7748777281029)
- -1741311 | Serbia | -214885 | Croatia | 99 | POINT(18.9058620655204 45.7703427407642)
- -449220 | Taiwan | -270056 | China | 90 | POINT(119.965674546357 25.9662536685956)
- -1703814 | Palestinian Territories | -1473946 | Israel | 70 | POINT(35.2246457423727 31.7840924565514)
- -449220 | Taiwan | -270056 | China | 53 | POINT(120.226351993941 26.3394680962033)
- -449220 | Taiwan | -270056 | China | 52 | POINT(120.488227295244 26.3688691722675)
- -218657 | Slovenia | -214885 | Croatia | 45 | POINT(13.4763409023349 45.5471286535986)
- -449220 | Taiwan | -270056 | China | 19 | POINT(119.462511680242 24.9902599054372)
- -2171347 | Luxembourg | -51477 | Germany | 7 | POINT(6.37445942266256 49.7263622848021)
- -252645 | Bolivia | -59470 | Brazil | 5 | POINT(-65.3571669145145 -10.7930812644583)
- -443174 | Philippines | -270056 | China | 2 | POINT(114.112730361287 10.9441562730741)
- -2202162 | France | -365331 | Italy | 1 | POINT(6.86265648948385 45.8332959568394)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement