A shipping-line analyst is scoping a coastal-capital service and needs every pairing of a national capital with a seaport within 25 km — the distance a container can move by truck inside an hour. The first attempt filtered ST_Distance in degrees and returned pairings 25 degrees apart. The second cast everything to geography inside a function and ran for a minute. It has to be right, in metres, and it has to use the index.
Return every (capital, port) pair where the port is within 25 km of the capital.
| column | meaning |
|---|---|
capital | the place's name |
port | the port's name |
distance_m | great-circle distance in metres, rounded to the nearest metre |
geom | the port's point, so the result draws on the map |
The tables are populated_places and ports, both SRID 4326 with a GiST index on geom; their columns are below. A capital is a row with capital = true.
Use ST_DWithin on geography so the 25 000 is metres and the planner can use the index. Order by capital, then distance_m.
populated_places — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
place_id | text | PL- plus the Natural Earth id |
name | text | Place name |
country | text | Country the place is in, as an attribute |
iso_a2 | text | Two-letter country code, as an attribute; -99 where Natural Earth assigns none |
featurecla | text | Admin-0 capital | Admin-1 capital | Populated place | Scientific station | … |
capital | boolean | True for a national capital |
pop_max | integer | Upper population estimate (persons) |
pop_min | integer | Lower population estimate (persons) |
lat_attr | double precision | Natural Earth's LATITUDE attribute (degrees) |
lon_attr | double precision | Natural Earth's LONGITUDE attribute (degrees) |
timezone | text | IANA time zone |
geom | geometry(Geometry, 4326) | Point |
ports — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
port_id | text | PT- plus the Natural Earth id |
name | text | Port name |
scalerank | integer | Prominence, lower is larger |
natlscale | integer | Natural Earth national-scale rank |
website | text | Port authority site, where known |
geom | geometry(Geometry, 4326) | Point |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
populated_placesCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| place_id | text |
| name | text |
| country | text |
| iso_a2 | text |
| featurecla | text |
| capital | boolean |
| pop_max | integer |
| pop_min | integer |
| lat_attr | double precision |
| lon_attr | double precision |
| timezone | text |
| geom | geometry(Geometry,4326) |
portsCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| port_id | text |
| name | text |
| scalerank | integer |
| natlscale | integer |
| website | text |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Distance-bounded joins on geography are how any global service-area question is answered in PostGIS, and the ST_DWithin form is the one that scales.
5 scored, 0 informational
Returns capital, port, distance_m
15%One row per qualifying pair
31%Uses ST_DWithin
23%Distance on geography
23%No ST_Distance in the filter
8%