Geospatial data represents locations, shapes, and relationships associated with places on Earth. In Postgres, PostGIS supports storing, querying, processing, and visualizing geodata such as Florida’s cities, roads, parks, landmarks, and natural features. section 10.0 section 10.1
Represent geographic objects using geometry types that match their real-world form:
- Use points for places of interest, amenities, landmarks, cafes, and restaurants, with descriptive attributes such as names and ratings. section 10.1
- Use lines for roads and other paths; a road may be split into multiple stored segments defined by several points. section 10.5.3
- Use polygons for buildings, parks, lakes, cities, states, and other bounded regions. section 10.1
- Treat geodata as static, dynamic, or temporal depending on whether locations or attributes change. section 10.1
Find major roads crossing Miami
WITH miami AS ( SELECT way AS boundaries FROM florida.planet_osm_polygon WHERE name = 'Miami' AND place = 'city' ) SELECT l.name, l.highway, ST_NPoints(l.way) AS points_number, round(ST_Length(l.way)) AS len_meters, round(ST_Length(l.way) * 3.28084) AS len_feet FROM florida.planet_osm_line l JOIN miami m ON ST_Intersects(l.way, m.boundaries) WHERE l.highway IN ('primary', 'secondary') ORDER BY len_meters DESC LIMIT 10; The result lists road names, road types, point counts, and lengths. Repeated names can indicate separate stored road segments. section 10.5.3
Figure 10.4 Areas in Florida to query on
Use the Florida trip map to orient spatial queries around Tampa, an amusement-park region, and Miami. section 10.5
