Columbia County FLA Import

This Import Plan Outline is intended to help ensure that your "Import plan" document covers as many of the common questions about imports as possible. Just create your own page and cut and paste the wiki text from here (starting from below the line)

Please! If you identify ways that this outline didn't meet the needs of your import (key evidence of this: tons of questions or alarm bells on mailing lists!), please return and fix this page.

---

(Columbia County FLA Building Footprints) is an import of Microsoft Building Footprints that map to county provided address points for Columbia County, FL in United States.

Current Status

The import is currently (3/1/2021) at the planning stage.

The import is currently (4/1/2021) at the peer review stage.

Completed (4/2/2021)

https://www.openstreetmap.org/changeset/102153624

https://www.openstreetmap.org/changeset/102218062

https://www.openstreetmap.org/changeset/102218334

https://www.openstreetmap.org/changeset/102218471

https://www.openstreetmap.org/changeset/102219898

https://www.openstreetmap.org/changeset/102219834

https://www.openstreetmap.org/changeset/102219829

https://www.openstreetmap.org/changeset/102219732

https://www.openstreetmap.org/changeset/102219730

https://www.openstreetmap.org/changeset/102219688

https://www.openstreetmap.org/changeset/102219692

Goals

The goal is to provide addressed building footprints for Columbia County, FL. USA.

Since the address data in Columbia County is based on rooftop, was able to extract footprints from the MS building foot print data set for a large amount of address points.

Remaining address points will be imported at a later time.

Schedule

February and March 2021 - prepare data for import and test.

The import will be prepared and ready for delivery within April 2021.

Import Data

• Unprepared (Not filtered) MS Building Footprints (Used for prep)
  • https://www.dropbox.com/s/ontdxpw58tdvylu/Microsoft%20Building%20Footprints%20for%20Columbia%20County.zip?dl=0

• Address points (Used for prep)
  • https://www.dropbox.com/s/inm3t3odxeqkftr/Addresses%20%289%29.zip?dl=0

• (Actual data for import) Prepared MS building footprints for import. OSM format. One OSM per zip code.
  • https://www.dropbox.com/s/d3jux9zpiz4k29c/Columbia%20County%20Building%20Footprints%20By%20ZipCodes%20OSM%20Files.zip?dl=0

• Sample: https://www.openstreetmap.org/changeset/102153624

Import Type

The is a one time import using a combination of QGIS, MSSQL, C# and JOSM to do the work.

Data Preparation

• Import MS Building Footprints polygons, Address points, and existing OSM building polygons into MSSQL and use spatial queries to filter and prepare the data.
  • Note: OSM building ways should be converted to a polygon on import.
  • Update building footprint for address ==> update Addresses set OsmBuildingFootPrint = f.geom.STAsText(), WayID = null, geom = f.geom from Addresses a join MSBuildingFootprints f on f.geom.STBuffer(.00003).STContains(a.geom) = 1
  • Update addresses with WayID for existing buildings on the map ==> update Addresses set WayID = w.Wayid from Addresses a join OsmWays w on w.geom.STIntersects(a.geom) = 1 or w.geom.STTouches(a.geom) = 1 where a.WayID is null

• Query converted street names on addresses and compare to street names in OSM. Corrected over 400 street names on OSM. Most were missing prefixes and/or suffixes and some were wrong.
• Created missing streets in OSM that did not exist or was combined into an existing street (split way and renamed a section to the correct street name).
• Provided a common name for some main roads to match address. Example: US 90 -> West US Highway 90.

Data Reduction & Simplification

• Eliminate all footprints that do not contain an address point or that intersect or touch an existing OSM building or street.

Tagging Plans

• Building footprints with a singular address will contain address tags provided by county address data. Buildings with multiple addresses will not be tagged with address tags and will be manually adjusted later.
  • Tags included:
    • addr:housenumber
    • addr:street
    • addr:city
    • addr:state
    • addr:postcode
    • building:yes

• Street names are converted from a source that uses uppercase letters and abbreviations for directions and suffix and are verified against existing OSM street names. Adjustments made as necessary.

• After import, use http://tools.geofabrik.de/osmi/ to discover and correct remaining errors.

Changeset Tags

Changeset will contain a source tag with the value Columbia County.

• created_by: Columbia County
• documentation: Columbia County FLA Import

Data Merge Workflow

• Open generated OSM file(s) in JOSM.
• Run validation on OSM and fix any issues.
• Merge data layer and run validation again. Fix any issues.
• Upload changeset using JOSM.

Team Approach

Although this is a solo effort, I've been in constant communication with a community member (and C# coder) who has done Import/Maine E911 Addresses - OpenStreetMap Wiki.

References

List all factors that will be evaluated in the import.

Workflow

One changeset per zipcode.

32096 -> 600 addresses

32055 -> 9267 addresses

32061 -> 173 addresses

32643 -> 718 addresses

32025 -> 8861 addresses

32024 -> 8836 addresses

32094 -> 116 addresses

32038 -> 5767 addresses

WayIDs will be stored on imported record.

Conflation

Building footprints that touch, intersect, or would otherwise interfere with existing buildings or streets on map are not included.

Building footsprints that contain multiple address points will only be used once and not tagged with address information (manually adjusted later).

Addresses will not contain Unit numbers.

QA

Data will be QA'd using QGIS and spatial queries prior to import.

Data will be tested and validated in JOSM prior to import.

http://tools.geofabrik.de/osmi/ will be used after import..

See also

The email to the Imports mailing list was sent on YYYY-MM-DD and can be found in the archives of the mailing list at [1].