giacecco

Couldn't load plugin 'processing' from ['/Applications/QGIS.app/Contents/MacOS/../Resources/python/plugins/processing', '/Applications/QGIS.app/Contents/MacOS/../Resources/python', '/Users/giacecco/.qgis2/python', '/Users/giacecco/.qgis2/python/plugins', '/Applications/QGIS.app/Contents/MacOS/../Resources/python/plugins', '/Library/Frameworks/SQLite3.framework/Versions/C/Python/2.7', '/Library/Frameworks/GEOS.framework/Versions/3/Python/2.7/site-packages', '/Library/Python/2.7/site-packages/numpy-override', '/Library/Python/2.7/site-packages/matplotlib-override', '/Library/Frameworks/GDAL.framework/Versions/1.11/Python/2.7/site-packages', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python27.zip', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-darwin', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac', '/System/Library/Frameworks/Python.framework/Versions/2.7

giacecco

.../OLAF-import-open_roads$ node import.js --in ~/Data\ archive/os/open-roads/1506120854/
Importing /Users/giacecco/Data archive/os/open-roads/1506120854/oproad_essh_gb.zip...
Shapefile type: Point
Postgis type: POINT[2]
ERROR:  column not found in geometry_columns table
CONTEXT:  SQL statement "SELECT DropGeometryColumn('',$1,$2,$3)"
PL/pgSQL function dropgeometrycolumn(character varying,character varying,character varying) line 5 at SQL statement
ERROR:  table "os_open_roads_roadnodes" does not exist

Done.

giacecco

.../OLAF-import-open_roads$ node import.js --in ~/Data\ archive/os/open-roads/1506120854/
Importing /Users/giacecco/Data archive/os/open-roads/1506120854/oproad_essh_gb.zip...
NOTICE:  table "os_open_roads_roadnodes" does not exist, skipping
NOTICE:  table "os_open_roads_roadlinks" does not exist, skipping
Shapefile type: Point
Postgis type: POINT[2]
ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(os_open_roads_roadnodes_gid_seq, 2200) already exists.
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]

giacecco

DROP TABLE IF EXISTS temp_retired_postcodes;
CREATE TABLE temp_retired_postcodes AS (
	SELECT DISTINCT pcd
	FROM lr_9501_1504
	WHERE pcd NOT IN (SELECT pcd FROM temp_active_postcodes)
);
SELECT COUNT(pcd) FROM temp_retired_postcodes;

giacecco

DROP TABLE IF EXISTS temp_active_postcodes;
CREATE TABLE temp_active_postcodes AS (
	SELECT DISTINCT id AS pcd 
	FROM os_open_names
	WHERE local_type = 'Postcode' AND country IN ('England', 'Wales')
);
SELECT COUNT(pcd) FROM temp_active_postcodes;

giacecco

SELECT COUNT(pcd) FROM ons_pd
WHERE doterm IS NOT NULL AND COUNTRY IN ('e', 'w');

giacecco

SELECT COUNT(*)
FROM lr_9501_1504
WHERE pcd IN (SELECT pcd FROM temp_retired_postcodes);

giacecco

DROP TABLE IF EXISTS pcd_fix;
CREATE TABLE pcd_fix AS
	SELECT old_pcd, new_pcd, distance_meters, distance_degrees
	FROM 

		(SELECT pcd AS old_pcd, geom
		FROM ons_pd
		WHERE doterm IS NOT NULL) AS a,

		LATERAL (SELECT pcd AS new_pcd, ST_Distance_Sphere(a.geom, geom) AS distance_meters, ST_Distance(a.geom, geom) AS distance_degrees  

giacecco

DROP TABLE IF EXISTS output;
CREATE TABLE output (no_of_records INTEGER);
SELECT parsel(
	'olaf', 
	'ons_pd',
	'id',
	'select count(a.id) as no_of_records;',
	'output',
	'a',
	2);

giacecco

psql:fix_pdc.sql:34: ERROR:  function fix_pdc() does not exist
LINE 1: SELECT fix_pdc();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.