Sample Queries

Scalar Values

Below are some sample queries that return scalar values from the OSGeoLive PostgreSQL database.

Obtain the area of the first 10 cities:

SELECT gid,ST_Area(geom)
FROM citypars83_12_2010_public
LIMIT 10

 

Geometries

Below are some sample queries that return geometries from the OSGeoLive PostgreSQL database.

Obtain geometries for the first 10 cities and buffer the geometries by 10,000 units.

SELECT gid,ST_AsText(ST_Buffer(geom,10000))
FROM citypars83_12_2010_public
LIMIT 10

Select the geometry for the USA:

SELECT geom
FROM ne_10m_admin_0_countries
WHERE LOWER(admin) LIKE '%united states of america%'

Find the river and lake centerlines that intersect with the USA. We do this by including both tables in the "FROM" statement and then filtering to only include centerlines that interesect with the country geometries and filter the country geometries by "united states of america".

SELECT ST_AsText(ne_10m_rivers_lake_centerlines.geom),ne_10m_rivers_lake_centerlines.gid
FROM ne_10m_rivers_lake_centerlines,ne_10m_admin_0_countries
WHERE ST_Intersects(ne_10m_rivers_lake_centerlines.geom,ne_10m_admin_0_countries.geom)
 AND LOWER(ne_10m_admin_0_countries.admin) LIKE '%united states of america%'
 LIMIT 10

 

More Resources