[Talk-GB] UPRN Locations Map
ndrw
ndrw6 at redhazel.co.uk
Mon Jul 6 16:31:04 UTC 2020
On 02/07/2020 17:38, Robert Whittaker (OSM lists) wrote:
> I'm not completely sure if/how we can best make use of the new OS
> OpenData (UPRNs, USRNs and related links) in OpenStreetMap, but as a
> first step I've set up a quick slippy map with the UPRN locations
> shown:
>
> https://osm.mathmos.net/addresses/uprn/ (zoom in to level 16 to show the data)
I've been reading about the geopackage file format used for encoding
UPRN and USRN data and played with the databases a bit. Below is a
summary of what I've learned.
Geopackage files are really sqlite databases with a gpgk extension. The
extension can be downloaded from
https://bitbucket.org/luciad/libgpkg/src/default/ and compiled. The
extension is needed for accessing geometry values (blobs), a similar
concept to the one used by the mod_spatialite extension but not
compatible with it.
The gpgk_contents table serves as a main entry point specifying the name
of the table containing data, last change date, spatial extents along
with a spatial reference system (here EPSG27700/OSGB1936).
Gpgk_geometry_columns specifies names of the table+column holding
geometric data, their types, their spatial reference system, z
(elevation) and m (measure) values. The latter two are not used in these
files.
Gpkg_spatial_ref_sys provides definitions of WGS84 and OSGB1936
reference systems, only the latter is used by the data tables.
Gpkg_tile_matrix and gpkg_tile_matrix_set tables are empty, no
pre-generated tiles.
Tables with names starting with rtree_ are spatial indices for geometric
data. This is defined by an extension to GeoPackage
(http://www.geopackage.org/spec120/#extension_rtree) - I haven't tried
using this feature yet.
1. UPRN file
This file contains UPRNs and their locations (Point) in the
osopenuprn_address table. In addition to the GEOM field containing point
coordinates it duplicates the coordinates in separate fields in both
OSGB1936 and WGS84 formats. So, if necessary, this file can be used
without the pgkg extension. Unfortunately, despite fancy packaging it is
still "a proprietary number+coordinates" data format, so, unless there
are additional open data available specifying the meaning of each UPRN,
UPRNs are (IMHO) only useful as a reference.
Script for accessing contents of the geopackage file:
#!/bin/sh
sqlite3 -batch -echo osopenuprn_202006.gpkg <<EOF
select load_extension('/usr/local/lib/libgpkg.so',
'sqlite3_gpkg_auto_init');
.headers on
.mode column
.tables
select * from gpkg_contents limit 10;
select * from gpkg_extensions limit 10;
select * from gpkg_geometry_columns limit 10;
select * from gpkg_spatial_ref_sys limit 10;
select *,ST_AsText(GEOM) from osopenuprn_address limit 10;
EOF
output of the above script:
select load_extension('/usr/local/lib/libgpkg.so',
'sqlite3_gpkg_auto_init');
.headers on
.mode column
.tables
gpkg_contents osopenuprn_address
gpkg_extensions rtree_osopenuprn_address_geom
gpkg_geometry_columns rtree_osopenuprn_address_geom_node
gpkg_spatial_ref_sys rtree_osopenuprn_address_geom_parent
gpkg_tile_matrix rtree_osopenuprn_address_geom_rowid
gpkg_tile_matrix_set
select * from gpkg_contents limit 10;
table_name data_type identifier description
last_change min_x min_y max_x max_y srs_id
------------------ ---------- ------------------ -----------
------------------------ ---------- ---------- ---------- ----------
----------
osopenuprn_address features osopenuprn_address
2020-06-18T03:16:58.866Z 0.0 0.0 700000.0 130000.0
27700
select * from gpkg_extensions limit 10;
table_name column_name extension_name
definition scope
------------------ ----------- ----------------
-------------------------------------------------- ----------
osopenuprn_address geom gpkg_rtree_index
http://www.geopackage.org/spec120/#extension_rtree write-only
select * from gpkg_geometry_columns limit 10;
table_name column_name geometry_type_name srs_id z m
------------------ ----------- ------------------ ----------
---------- ----------
osopenuprn_address geom POINT 27700 0 0
select * from gpkg_spatial_ref_sys limit 10;
srs_name srs_id organization
organization_coordsys_id definition description
----------------------- ---------- ------------
------------------------ ----------
-----------------------------------------------
Undefined cartesian SRS -1 NONE -1
undefined undefined cartesian coordinate reference system
Undefined geographic SR 0 NONE 0
undefined undefined geographic coordinate reference syste
WGS 84 geodetic 4326 EPSG 4326
GEOGCS["WG longitude/latitude coordinates in decimal degre
OSGB 1936 / British Nat 27700 EPSG 27700 PROJCS["OS
select *,ST_AsText(GEOM) from osopenuprn_address limit 10;
FID GEOM UPRN X_COORDINATE Y_COORDINATE
LATITUDE LONGITUDE ST_AsText(GEOM)
---------- ---------- ---------- ------------ ------------
---------- ---------- ---------------------------
1 GP 1 358263.47 172798.15 51.4526159
-2.6020347 Point (358263.47 172798.15)
2 GP 26 352967.0 181077.0 51.5266333
-2.6793612 Point (352967 181077)
3 GP 27 352967.0 181077.0 51.5266333
-2.6793612 Point (352967 181077)
4 GP 30 354800.0 180469.0 51.5213173
-2.6528615 Point (354800 180469)
5 GP 31 354796.0 180460.0 51.521236
-2.652918 Point (354796 180460)
6 GP 32 353473.0 180409.0 51.5206696
-2.671979 Point (353473 180409)
7 GP 33 352548.0 180308.0 51.5196842
-2.6852966 Point (352548 180308)
8 GP 34 352515.0 180360.0 51.5201489
-2.6857792 Point (352515 180360)
9 GP 38 352462.0 180401.0 51.5205131
-2.6865486 Point (352462 180401)
10 GP 41 354662.0 180364.0 51.5203621
-2.6548369 Point (354662 180364)
2. USRN file
This file contains USRNs, geometry (LineString or MultiLineString) and a
"street type" field. Because geometry data are more complex there are no
extra columns with X/Y coordinates like there were in the UPRN file, so
the gpkg extension is required for accessing geometries. With
(presumably) street geometries and street types we may be able to
correlate USRN data with OSM objects, which may be quite useful.
Script for accessing contents of the geopackage file:
#!/bin/sh
sqlite3 -batch -echo osopenusrn_202007.gpkg <<EOF
select load_extension('/usr/local/lib/libgpkg.so',
'sqlite3_gpkg_auto_init');
.headers on
.mode column
.tables
select * from gpkg_contents limit 10;
select * from gpkg_extensions limit 10;
select * from gpkg_geometry_columns limit 10;
select * from gpkg_spatial_ref_sys limit 10;
select *,ST_AsText(geometry) from openUSRN limit 10;
EOF
Output of the above script:
select load_extension('/usr/local/lib/libgpkg.so',
'sqlite3_gpkg_auto_init');
.headers on
.mode column
.tables
gpkg_contents openUSRN
gpkg_extensions rtree_openUSRN_geometry
gpkg_geometry_columns rtree_openUSRN_geometry_node
gpkg_spatial_ref_sys rtree_openUSRN_geometry_parent
gpkg_tile_matrix rtree_openUSRN_geometry_rowid
gpkg_tile_matrix_set
select * from gpkg_contents limit 10;
table_name data_type identifier description
last_change min_x min_y max_x max_y srs_id
---------- ---------- ---------- -----------
------------------------ ---------- ---------- ---------- ----------
----------
openUSRN features openUSRN 2020-06-22T19:34:59.303Z 80699.0
6443.01 655563.0 657489.0 27700
select * from gpkg_extensions limit 10;
table_name column_name extension_name
definition scope
---------- ----------- ----------------
-------------------------------------------------- ----------
openUSRN geometry gpkg_rtree_index
http://www.geopackage.org/spec120/#extension_rtree write-only
select * from gpkg_geometry_columns limit 10;
table_name column_name geometry_type_name srs_id z m
---------- ----------- ------------------ ---------- ----------
----------
openUSRN geometry GEOMETRY 27700 2 2
select * from gpkg_spatial_ref_sys limit 10;
srs_name srs_id organization
organization_coordsys_id definition description
----------------------- ---------- ------------
------------------------ ----------
-----------------------------------------------
Undefined cartesian SRS -1 NONE -1
undefined undefined cartesian coordinate reference system
Undefined geographic SR 0 NONE 0
undefined undefined geographic coordinate reference syste
WGS 84 geodetic 4326 EPSG 4326
GEOGCS["WG longitude/latitude coordinates in decimal degre
OSGB 1936 / British Nat 27700 EPSG 27700 PROJCS["OS
select *,ST_AsText(geometry) from openUSRN limit 10;
id geometry usrn street_type ST_AsText(geometry)
---------- ---------- ---------- ---------------
--------------------------------------------------------------------------------------------------------------------
1 GP 80866008 Numbered Street MultiLineString Z
((362183.956 497984.711 0, 362291 498500 0, 362282 498682 0, 362237
498849 0, 362161 499000 0, 362091 499094 0, 361779 499359 0, 361635
499514 0, 361423 499837 0, 361076 500269 0, 360962.571 500463.114 0,
360866.117 500714.865 0, 360827 500913 0, 360808 501147 0, 360835 501429
0), (360853.378 501426.439 0, 360828.
2 GP 22502177 Designated Stre LineString Z
(532719 178729 0, 532704 178780 0)
3 GP 38205754 Designated Stre MultiLineString Z
((442078.613 521725.502 0, 442026.171 521711.664 0), (442096.534
521730.023 0, 442133.237 521739.996 0))
4 GP 14000277 Designated Stre MultiLineString Z
((450083 333067 0, 450175.743 333017.427 0), (450014.727 333103.947 0,
450083 333067 0))
5 GP 39900588 Designated Stre LineString Z
(624754 220847 0, 624731 220878 0, 624736 220916 0, 624868.817
221064.643 0, 624877.217 221081.443 0)
6 GP 46704645 Officially Desc MultiLineString Z
((305682.56 379229.223 0, 305671.95 379215.29 0, 305667.17 379222.355 0,
305658.865 379218.81 0, 305657.4946 379229.4179 0), (305657.4946
379229.4179 0, 305639.585 379257.196 0, 305620.248 379257.508 0),
(305682.56 379229.223 0, 305683.83 379243.82 0, 305691.995 379251.89 0,
305728.366 379245.604 0))
7 GP 38605976 Designated Stre MultiLineString Z
((621678.176 262307.787 0, 621997.891 262483.919 0), (621997.891
262483.919 0, 622266.504 262633.251 0))
8 GP 38301618 Officially Desc MultiLineString Z
((437887.105 247434.337 0, 437695 247385 0, 437609 247331 0, 437547.553
247270.353 0), (438082.33 247485.479 0, 437986 247451 0, 437887.105
247434.337 0))
9 GP 27001252 Designated Stre MultiLineString Z
((385826.992 355006.375 0, 385869 355028 0, 385973.059 355102.736 0,
385984 355122 0), (385707.235 354941.371 0, 385826.992 355006.375 0),
(385638 354862 0, 385672 354919 0, 385707.235 354941.371 0), (386010.09
355109.414 0, 385984 355122 0), (385984 355122 0, 385964.7 355133.9 0))
10 GP 23509653 Designated Stre MultiLineString Z
((498334 371964 0, 498349 371945 0, 498351 371919 0), (498312.4
371964.796 0, 498334 371964 0), (498334 371964 0, 498338.4 371982.796 0))
I hope that's useful.
More information about the Talk-GB
mailing list