Mathare Building import
From Map Kibera
(→Preparing the database for the new data) |
|||
Line 161: | Line 161: | ||
*shop | *shop | ||
*source | *source | ||
+ | |||
+ | == Preparing the database for the new data == | ||
+ | |||
+ | DROP TABLE IF EXIST mathare_building_import; | ||
+ | |||
+ | CREATE TABLE mathare_building_import | ||
+ | ( | ||
+ | osm_id integer, | ||
+ | "access" character varying(50), | ||
+ | "amenity" character varying(50), | ||
+ | "building" character varying(50), | ||
+ | "building:type" character varying(50), | ||
+ | "fixme" character varying(250), | ||
+ | "health_facility:type" character varying(50), | ||
+ | "motor_vehicle" character varying(50), | ||
+ | "name" character varying(50), | ||
+ | "note" character varying(250), | ||
+ | "office" character varying(50), | ||
+ | "religion" character varying(50), | ||
+ | "shop" character varying(50), | ||
+ | "source" character varying(50), | ||
+ | the_geom geometry, | ||
+ | CONSTRAINT goundtruthed84_pkey PRIMARY KEY (gid), | ||
+ | CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), | ||
+ | CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL), | ||
+ | CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3857) | ||
+ | ) WITH ( OIDS=FALSE ); | ||
== Generate the OSM data to be imported == | == Generate the OSM data to be imported == |
Revision as of 14:32, 16 May 2011
In this page, I (Sébastien) will describe how to import the data of the building extraction of Mathare that we've got from AAAS. See Primož's blog for the initial analysis.
Contents |
Changing the SRS
The data was made available as shapefiles in WGS84 Web Mercator Auxiliary Sphere projection, which is known as Web Mercator, spherical Mercator, Google Mercator and is referenced by various ids: EPSG:900913, EPSG:3785 (obsolete), EPSG:3857, ESRI WKID 102100... In QGIS, it's known as Google Mercator, EPSG:900913, ID 3644.
Before working with the data, we need to change the Spatial Reference System (SRS) to be compatible with OSM, that is moving to non-projected WGS84.
In QGIS: menu Vector -> Data Management Tools -> Export to new projection. Then choose the output CRS to be WGS84, EPSG:4326, ID 3452.
The two files that we're going to import are GroundTruthed-wgs84.shp and BuildingsOnTop-wgs84.shp
Setup PostGIS database
PostGIS is relatively easy to install on Ubuntu. I might describe it further later. Anyway, the current environment is PostgreSQL 8.4, PostGIS 1.5.
I found this script from GeoDjango very convenient to create a postgis db template:
$ ./create_template_postgis-debian.sh $ createdb -T template_postgis mathare
Import the shapefiles to the db
Each shapefile will be imported a separate table with shp2pgsql:
shp2pgsql -s 4326 -c -g the_geom -I -S -W UTF-8 -N skip Groundtruthed-wgs84.shp | psql mathare shp2pgsql -s 4326 -c -g the_geom -I -S -W UTF-8 -N skip BuildingsOnTop-wgs84.shp | psql mathare
Import OSM data to the db
In this case, we're only interested in buildings from OSM.
Get fresh data from OSM, keep only ways tagged with 'building' and their associated ways:
osmosis \ --read-api left=36.8425655 right=36.881876 top=-1.2480512 bottom=-1.2696752999999998 outPipe.0=1 \ --way-key keyList=building inPipe.0=1 outPipe.0=2 \ --used-node inPipe.0=2 outPipe.0=3 \ --write-xml file=mathare_building.osm inPipe.0=3
Import it to the database:
osm2pgsql -c -d mathare -l -s mathare_building.osm
The following tables will be created:
- planet_osm_line
- planet_osm_nodes
- planet_osm_point
- planet_osm_polygon
- planet_osm_rels
- planet_osm_roads
- planet_osm_ways
Find collisions between OSM and the import candidate
Very few buildings have already been mapped in OSM in Mathare (139 as of 2011.05.09). Yet, we should be careful whilst importing the dataset from aerial imagery extraction. While we can discard the geometry of the existing buildings, we shall preserve their attributes.
Basically, we shall check for geometries that overlaps and apply the attributes of the OSM element to the imported one.
Note: I can see two tricky corner cases, which I don't need to worry about right now since this import does not feature them. However, overlooking these issues in another import will likely result in a loss of data:
- nodes of the buildings bear attributes
- multipolygons
We can find the overlapping elements with this query:
mathare#= select g.gid, o.osm_id, from "groundtruthed-wgs84" as g, planet_osm_polygon as o where ST_Intersects(g.the_geom, o.way) ORDER BY g.gid;
Tagging scheme
Every geometry imported will be tagged with:
source=AAAS_satellite_extraction_for_Mathare_2011
Shapefile column | OSM tags |
---|---|
id | ignored, since not used in shapefiles |
designated | see table below |
type=* | building:type=* |
name=* | name=* |
Shapefile Designated=* | OSM tags |
---|---|
Toilet | building=yes amenity=toilet |
NGO/CBO | building=office office=ngo |
Administration | building=office office=government |
CBO | building=office office=ngo |
Self-Help Group | building=yes note=Self-Help Group fixme=Find an appriopriate tag for "Self help group" |
Medical Clinic | building=yes amenity=hospital health_facility:type=medical_clinic |
Business | building=commercial |
Petrol Station | building=yes amenity=fuel motor_vehicle=yes |
House | building=residential |
Apartment Building | building=apartments |
Hardware Shop | building=commercial shop=hardware |
Church | building=church amenity=place_of_worship religion=christian |
Bar | building=yes amenity=bar |
Bathroom | building=yes amenity=shower |
Private Toilet | building=yes amenity=toilet access=private fixme=mathare preset scheme |
Garage | building=garage |
Public Toilet | building=yes amenity=toilet access=public |
Market Place |
building=yes |
Cyber Cafe | building=yes amenity=cyber_cafe |
Keys:
- access
- amenity
- building
- building:type
- fixme
- health_facility:type
- motor_vehicle
- name
- note
- office
- religion
- shop
- source
Preparing the database for the new data
DROP TABLE IF EXIST mathare_building_import; CREATE TABLE mathare_building_import ( osm_id integer, "access" character varying(50), "amenity" character varying(50), "building" character varying(50), "building:type" character varying(50), "fixme" character varying(250), "health_facility:type" character varying(50), "motor_vehicle" character varying(50), "name" character varying(50), "note" character varying(250), "office" character varying(50), "religion" character varying(50), "shop" character varying(50), "source" character varying(50), the_geom geometry, CONSTRAINT goundtruthed84_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3857) ) WITH ( OIDS=FALSE );