[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