3

Ordnance Survey Open Names data comes as a zipped directory containing header-less CSVs for each 10km OS Grid square. The headers are in another, one line CSV. The data contains various attributes and separate X and Y coordinates.

Is it possible to create a .vrt from this data without unzipping it (zipped size < 100MB, unzipped = GBs if you want to know why!)? The bit I'm not is possible is getting it to use the header file.

I have managed to create a virtual raster of zipped OS Terrain 50 data (zip directory of gridded .asc files) using the method shown here: https://gis.stackexchange.com/a/110018/64402 with a couple of tweaks.


EDIT: Well I went all in and it didn't work so I thought I'd start simple and build up from there:

<OGRVRTDataSource>
    <OGRVRTUnionLayer name="OS Open Names">
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>EPSG:27700</LayerSRS>
        <FieldStrategy>FirstLayer</FieldStrategy>
        <OGRVRTLayer name="OS_Open_Names_Header">
            <GeometryField encoding="PointFromColumns" x="GEOMETRY_X" y="GEOMETRY_Y"/>
            <SrcDataSource relativeToVRT="1">opname\DOC\OS_Open_Names_Header.csv</SrcDataSource>
        </OGRVRTLayer>
        <OGRVRTLayer name="SO82">
            <SrcDataSource relativeToVRT="1">opname\DATA\SO82.csv</SrcDataSource>
        </OGRVRTLayer>
    </OGRVRTUnionLayer>
</OGRVRTDataSource>

This is obviously only with a header file and one data file (SO82) and neither are zipped. Now loading this into QGIS, creates a point file and the attribute table has all the right headers and there are even the same number of rows as in the data file. Unfortunately, they are all empty and no points actually render (as GEOMETRY_X and GEOMETRY_Y haven't been populated). What've I done wrong?

7
  • 1
    You have to build vector VRT files manually, gdal.org/drv_vrt.html should give some hints. Headless CSV get field names field_1 etc, and the VRT should map those according to the header column file information.
    – AndreJ
    Commented Feb 7, 2017 at 10:09
  • Was hoping I could build automagically, oh well. Once I get one working I shall post a link to it - it'll be useful for many. Where's the best place to stick it? Commented Feb 7, 2017 at 12:00
  • Does /vsizip/ work with vector VRT files? It does for rasters Commented Feb 7, 2017 at 12:27
  • I was planning on self-answering but I can't post the vrt in an answer because it's over 700 lines long! Commented Feb 7, 2017 at 19:21
  • Your UnionLayer can not work. As I wrote before, headless fields are named field_1, field_2 etc, so you have to tell the VRT driver which columns are X and Y.
    – AndreJ
    Commented Feb 7, 2017 at 20:38

2 Answers 2

3

This VRT file, created from the original headers file, works for me:

<OGRVRTDataSource>
<OGRVRTLayer name="SO60">
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:27700</LayerSRS>     
        <SrcDataSource relativeToVRT="1">SO60.csv</SrcDataSource>
          <Field name="ID" src="field_1" />
          <Field name="NAMES_URI" src="field_2" />
          <Field name="NAME1" src="field_3" />
          <Field name="NAME1_LANG" src="field_4" />
          <Field name="NAME2" src="field_5" />
          <Field name="NAME2_LANG" src="field_6" />
          <Field name="TYPE" src="field_7" />
          <Field name="LOCAL_TYPE" src="field_8" />
          <Field name="GEOMETRY_X" src="field_9" type="Real" width="9" precision="2" />
          <Field name="GEOMETRY_Y" src="field_10" type="Real" width="9" precision="2" />
          <Field name="MOST_DETAIL_VIEW_RES" src="field_11" />
          <Field name="LEAST_DETAIL_VIEW_RES" src="field_12" />
          <Field name="MBR_XMIN" src="field_13"  type="Real" width="10" precision="3" />
          <Field name="MBR_YMIN" src="field_14"  type="Real" width="10" precision="3" />
          <Field name="MBR_XMAX" src="field_15"  type="Real" width="10" precision="3" />
          <Field name="MBR_YMAX" src="field_16"  type="Real" width="10" precision="3" />
          <Field name="POSTCODE_DISTRICT" src="field_17" />
          <Field name="POSTCODE_DISTRICT_URI" src="field_18" />
          <Field name="POPULATED_PLACE" src="field_19" />
          <Field name="POPULATED_PLACE_URI" src="field_20" />
          <Field name="POPULATED_PLACE_TYPE" src="field_21" />
          <Field name="DISTRICT_BOROUGH" src="field_22" />
          <Field name="DISTRICT_BOROUGH_URI" src="field_23" />
          <Field name="DISTRICT_BOROUGH_TYPE" src="field_24" />
          <Field name="COUNTY_UNITARY" src="field_25" />
          <Field name="COUNTY_UNITARY_URI" src="field_26" />
          <Field name="COUNTY_UNITARY_TYPE" src="field_27" />
          <Field name="REGION" src="field_28" />
          <Field name="REGION_URI" src="field_29" />
          <Field name="COUNTRY" src="field_30" />
          <Field name="COUNTRY_URI" src="field_31" />
          <Field name="RELATED_SPATIAL_OBJECT" src="field_32" />
          <Field name="SAME_AS_DBPEDIA" src="field_33" />
          <Field name="SAME_AS_GEONAMES" src="field_34" />  
        <GeometryField encoding="PointFromColumns" x="field_9" y="field_10"/>        
</OGRVRTLayer>
</OGRVRTDataSource>

I used it on an unzipped file.

1
  • This was a great help and I wouldn't have solved the problem without it. However, it didn't address the issue of accessing zipped data or whether the header file could be used directly or not. Commented Feb 8, 2017 at 22:04
2

Right, so here goes. I haven't managed to achieve it with a single VRT (explanation to follow) but I have with two. The first VRT to is in this format:

<OGRVRTDataSource>
    <OGRVRTUnionLayer name="OS_Open_Names_Data">
        <!-- Repeat this section for each file -->
        <OGRVRTLayer name="SO82">
            <SrcDataSource relativeToVRT="0">/vsizip/full/absolute/path/with/forward/slashes/opname_csv_gb.zip/DATA/SO82.csv</SrcDataSource>
        </OGRVRTLayer>
        <!-- End of Repeat -->
    </OGRVRTUnionLayer>
</OGRVRTDataSource>

This returns a union of the zipped CSVs with default field nmes ("field_1" etc). The second VRT then references the first vrt in the <SrcDataSource> tag and converts it into useable data:

<OGRVRTDataSource>
    <OGRVRTLayer name="OS_Open_Names">
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>EPSG:27700</LayerSRS>     
        <SrcDataSource relativeToVRT="0">full/absolute/path/with/forward/slashes/OS_Open_Names_Data.xml</SrcDataSource>
        <SrcLayer>OS_Open_Names_Data</SrcLayer>
        <GeometryField encoding="PointFromColumns" x="field_9" y="field_10"/>
        <Field name="ID" src="field_1" />
        <Field name="NAMES_URI" src="field_2" />
        <Field name="NAME1" src="field_3" />
        <Field name="NAME1_LANG" src="field_4" />
        <Field name="NAME2" src="field_5" />
        <Field name="NAME2_LANG" src="field_6" />
        <Field name="TYPE" src="field_7" />
        <Field name="LOCAL_TYPE" src="field_8" />
        <Field name="GEOMETRY_X" src="field_9" type="Real" width="9" precision="2" />
        <Field name="GEOMETRY_Y" src="field_10" type="Real" width="9" precision="2" />
        <Field name="MOST_DETAIL_VIEW_RES" src="field_11" />
        <Field name="LEAST_DETAIL_VIEW_RES" src="field_12" />
        <Field name="MBR_XMIN" src="field_13"  type="Real" width="10" precision="3" />
        <Field name="MBR_YMIN" src="field_14"  type="Real" width="10" precision="3" />
        <Field name="MBR_XMAX" src="field_15"  type="Real" width="10" precision="3" />
        <Field name="MBR_YMAX" src="field_16"  type="Real" width="10" precision="3" />
        <Field name="POSTCODE_DISTRICT" src="field_17" />
        <Field name="POSTCODE_DISTRICT_URI" src="field_18" />
        <Field name="POPULATED_PLACE" src="field_19" />
        <Field name="POPULATED_PLACE_URI" src="field_20" />
        <Field name="POPULATED_PLACE_TYPE" src="field_21" />
        <Field name="DISTRICT_BOROUGH" src="field_22" />
        <Field name="DISTRICT_BOROUGH_URI" src="field_23" />
        <Field name="DISTRICT_BOROUGH_TYPE" src="field_24" />
        <Field name="COUNTY_UNITARY" src="field_25" />
        <Field name="COUNTY_UNITARY_URI" src="field_26" />
        <Field name="COUNTY_UNITARY_TYPE" src="field_27" />
        <Field name="REGION" src="field_28" />
        <Field name="REGION_URI" src="field_29" />
        <Field name="COUNTRY" src="field_30" />
        <Field name="COUNTRY_URI" src="field_31" />
        <Field name="RELATED_SPATIAL_OBJECT" src="field_32" />
        <Field name="SAME_AS_DBPEDIA" src="field_33" />
        <Field name="SAME_AS_GEONAMES" src="field_34" />
    </OGRVRTLayer>
</OGRVRTDataSource>

Why go to this trouble? Things I have learnt:

  1. visizip does work, but only with an absolute path. Therefore, <SrcDataSource> must have relativeToVRT attribute of "0". It is simply used by adding "/vsizip/" in front of the full path.
  2. <FieldStrategy>FirstLayer</FieldStrategy> means only fields found in the first layer will be included from the others. This is why using the header CSV is useless and part of the reason we have to explicitly set the field names in a second VRT.
  3. Within <OGRVRTUnionLayer> the src attribute doesn't function. This is the other reason we can't set the field names in the first VRT.
  4. Yes, if you read the documentation very carefully you could work this out (and I read it many many times), but it's not always obvious until you've tried it out.
  5. VRTs are amazing. The dataset in question here is huge, so I'll be using the <SrcSQL> to help filter it out before QGIS explodes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.