Working with OSM in PostGIS

PostGIS commands:

Log in to db

psql osmnode

Create user

su - postgres  
createuser -P -s -e osmnode  

Change owner of database

 ALTER DATABASE name OWNER TO new_owner;  

Rename column

ALTER TABLE osm_simplified_land_polygons RENAME COLUMN geom TO geometry;

Delete table

SELECT DropGeometryTable('osm_simplified_land_polygons');

Find SRID of table

SELECT Find_SRID('public', 'osm_land_polygons', 'geometry');  

Order by DESC/ASC

SELECT * FROM evil_bathymetry ORDER BY gid DESC;

Count number of rows in table

SELECT COUNT(*) FROM evil_bathymetry;

Lists all databases

\list

Lists all tables in the current database

\dt

Show all tables in a catalogue

\d planet_osm_line

Example query:

SELECT name, place, ST_XMin(way), ST_YMin(way) FROM planet_osm_point WHERE place='city' ORDER BY name;

Get some cities:

SELECT name FROM planet_osm_polygon WHERE boundary='administrative';

If you don't want the exact limits of the cities, but just a center point, look in osm_point, and use the column place:

SELECT name,way FROM planet_osm_point WHERE place IN ('town', 'city');

SELECT name FROM planet_osm_line WHERE highway IN ('motorway', 'trunk');

SELECT name from planet_osm_line where highway is not null and tunnel in ('yes','true','1');

Access: make sure db is trusted (NB! not secure?):

edit config file:

nano /etc/postgresql/9.4/main/pg_hba.conf

add this line:

local   all             all                                     trust

Import with imposm

Working with Imposm3

Repair geometries

First, create function.

CREATE OR REPLACE FUNCTION ST_Safe_Repair(
                                        geometry        geometry,
                                        message     text             default '[unspecified]'
                                        )
RETURNS geometry AS
$$
BEGIN
    IF geometry IS NULL THEN
        RAISE WARNING 'ST_Safe_Repair: geometry is NULL (%)', message;
        RETURN geometry;
    END IF;
    IF ST_IsEmpty(geometry) THEN
        RAISE DEBUG 'ST_Safe_Repair: geometry is empty (%)', message;
        -- empty POLYGON makes ST_Segmentize fail, replace it with empty GEOMETRYCOLLECTION
        RETURN ST_SetSRID('GEOMETRYCOLLECTION EMPTY'::geometry, ST_SRID(geometry));
    END IF;
    IF ST_IsValid(geometry) THEN
        RETURN ST_ForceRHR(ST_CollectionExtract(geometry, ST_Dimension(geometry) + 1));
    END IF;
    RETURN
        ST_CollectionExtract(
            ST_MakeValid(
                geometry
            ),
            ST_Dimension(geometry) + 1
        );
END
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Then, run on each table:

UPDATE osm_waterways SET geometry = ST_Safe_Repair(geometry);

---

References:

Some basic examples: https://gist.github.com/jpetazzo/5177554

Mapbox working with OSM: https://www.mapbox.com/tilemill/docs/guides/osm-bright-ubuntu-quickstart/

Schema reference: http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema

Postgis tuning: http://wiki.openstreetmap.org/wiki/User:Species/PostGIS_Tuning

Postgis async: https://github.com/mapnik/mapnik/wiki/Postgis-async

Some mapnik render optimiziations: https://github.com/mapnik/mapnik/wiki/OptimizeRenderingWithPostGIS

Maybe not relevant, but it's cool: http://wiki.openstreetmap.org/wiki/OSM_on_Paper

Mapnik SQL rendering toolchain performance: http://www.geofabrik.de/media/2010-07-10-rendering-toolchain-performance.pdf

PostGIS parameters:

Tools

GeoFabrik tile coordinates

Best practices

Optimizing PostGIS for production