Importing OpenStreetMap Data into MS SQL Server. After analyzing OSM extracts files, we can now import the data from XML into SQL Server.
Depending on your requirements, the table to hold the .osm data looks something like this: CREATE TABLE [dbo]. [OSM]( [Lat] [float] NOT NULL, [Lon] [float] NOT NULL, [Country] [nvarchar](50) NULL, [PostCode] [nvarchar](50) NOT NULL, [City] [nvarchar](200) NOT NULL, [Street] [nvarchar](200) NULL, [HouseNumber] [nvarchar](50) NULL ) Again, we start out reading from the .osm using an XmlReader using (var reader = new StreamReader(@"path\to\data.osm", Encoding.UTF8)) { foreach (var el in GetNextNode(reader)) { We collect all tag data (attributes k and v) in a dictionary var attrs = new Dictionary<string, string>(); foreach (var tag in el.Elements("tag")) { var k = tag.Attribute("k"); var v = tag.Attribute("v"); if (k !
If all required fields (in my case, addr:postcode and addr:city) are present, we generate the INSERT statement: Osm2mssql - OSM goes MS-SQL - Home. OpenStreetMap for MS SQL Server. Creating Squarified Heatmaps/Treemaps in SSRS. Quite often you’ll find to values you want to be able to compare visually against each other.
Sure, you can do this in basically any chart type you want to. However, one type of graph that is designed perfectly for this is a heatmap or treemap, like the image below. In this example, one value is represented by the size of the rectangle, while the other is reflected by the color. This can be adapted easily to only use the size if you only have one value. Now SSRS and BIDS don’t have this chart type natively available (yet) and I don’t know if they are planning to. Thanks to Richard Mintz’s Blog, I found a way to do this. I’ve made some minor changes to the data types, methods and T-SQL constructor to allow for 2 values instead of 1, but other than that, 99% of the code is Richard’s, so credit to him.
Richard’s Blog pretty much covers all of the steps, which are as follows: 1. 2. Also, for more information behind the mathematics behind this algorithm, see 1: public class TreeMapData. Report Builder 3 / SSRS does not support the ESRI PolygonZ element. FWTools: Open Source GIS/RS Binary Kit. Ogr2ogr. Converts simple features data between file formats Usage: Usage: ogr2ogr [--help-general] [-skipfailures] [-append] [-update] [-select field_list] [-where restricted_where] [-progress] [-sql <sql statement>] [-dialect dialect] [-preserve_fid] [-fid FID] [-spat xmin ymin xmax ymax] [-geomfield field] [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def] [-f format_name] [-overwrite] [[-dsco NAME=VALUE] ...] dst_datasource_name src_datasource_name [-lco NAME=VALUE] [-nln name] [-nlt type] [-dim 2|3|layer_dim] [layer [layer ...]]
This program can be used to convert simple features data between file formats performing various operations during the process such as spatial or attribute selections, reducing the set of attributes, setting the output coordinate system or even reprojecting the features during translation. -f format_name: output file format name (default is ESRI Shapefile), some possible values are: -f "ESRI Shapefile" -f "TIGER" -f "MapInfo File" -f "GML" -f "PostgreSQL"
OGR Comes to SQL Server 2008 Spatial. OGR, created by Frank Warmerdam, is an open source library and set of command line utilities for reading and writing geospatial vector data using many different formats .
It is the vector equivalent of GDAL which has similar functionality for rasters. The name of the library is a vestige from when OGR used to stand for OpenGIS Simple Features Reference Implementation. However as OGR is not fully compliant with the OpenGIS Simple Feature specification the name was changed to OGR Simple Features Library (from the GDAL FAQ). The latest additions to the OGR formats are the SQL Server 2008 geometry and geography. This enables spatial data in SQL Server 2008 to be reprojected, and converted to other formats (shapefiles, MapInfo, KML, GML, GeoJSON and any of the many other formats already supported by OGR) using freely available open source tools. The full MSSQLSpatial OGR driver notes and details are available online. Where can I get the Driver? Quick Set Up of OGR & MSSQLSpatial (on Windows) 1.