map-with-ai-postgis

Add datasets from map-with-ai to postgis and filter the major roads.

Dependencies:

• postgreSQL
• postgis
• ogr2ogr - GDAL

Data

• download country dataset from map-with-ai and unzip country_data.gpkg.tar
• convert country_data.gpkg to country_data.shp using ogr2ogr
  ogr2ogr -f "ESRI Shapefile" country_data.shp country_data.gpkg - where country_data is your data.
  
• If you see this error:
  Warning 1: 2GB file size limit reached for test.dbf. Going on, but might cause compatibility issues with third party software
  you will have to split your data into multiple shapefiles because of the 2GB .dbf limitation

DB

• connect to localhost
  psql -U userName;
• create new database
  CREATE DATABASE map_with_ai WITH ENCODING 'UTF8';
• connect to the created db
  \c map_with_ai;
• create postgis extension
  CREATE EXTENSION POSTGIS;

Load data into postgreSQL

• open a terminal in the folder where you have the country_data.shp
• import the country_data.shp to postgreSQL using shp2pgsql
  shp2pgsql -D -I -s 4326 country_data.shp | psql dbname=map_with_ai postgres

Filter data

• drop duplicated geometry column
  ALTER TABLE map_with_ai DROP COLUMN wkt;
• see number of cases grouped by highway tag column
  select highway_ta AS highway_tag,count(highway_ta) AS cases_count from map_with_ai GROUP BY highway_ta ORDER BY count(highway_ta);
• create new table with major roads
  create table output AS (select * from map_with_ai WHERE highway_ta IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link'));
• exit database
  exit;

Export data

• export the output table to shapefile format
  pgsql2shp -f output.shp -h localhost -u postgres -P password map_with_ai output
  where -P is your DB password