Each graphical representation is much more effective if spatial data is used. Tableau supports Spatial data, and .SHP files are the most commonly used source of data, but what are these?.

What is Shapefile?

The shapefile format is a geospatial vector data format for geographic information system (GIS) software. It is developed and regulated by Esri as a mostly open specification for data interoperability among Esri and other GIS software products. The shapefile format can spatially describe vector features: points, lines and polygons.

SHP Files (Point, Line, Area)

The term “shapefile” is quite common, but the format consists of a collection of files with a common filename prefix, stored in the same directory. The three mandatory files have filename extensions .shp, .shx, and .dbf.

A Spatial Reference System Identifier (SRID) is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS applications. Most often there is an .xml or .prj file to help us see which SRID we are dealing with.

SRID Definition

If it is a large amount of data, it should be transferred to the database, and Tableau supports direct connections to spatial data using these connectors:

  • Microsoft SQL Server
  • PostgreSQL + PostGIS
  • Pivotal Greenplum + PostGIS

When you need to accurately enter coordinates in a GIS, the first step is to uniquely define all coordinates on Earth.

This means you need a reference frame for your latitude and longitude coordinates because where would you be on Earth without having reference to it?

Because the Earth is curved – and in GIS we deal with flat map projections – we need to accommodate both the curved and flat views of the world. Surveyors and geodesists have accurately defined locations on Earth. More about map projections at https://en.wikipedia.org/wiki/List_of_map_projections and https://en.wikipedia.org/wiki/Map_projection

We begin modelling the Earth with an ellipsoid. To be clear, this is different from a geoid which is a measure of mean sea level. Over time, the ellipsoid has been estimated to the best of our ability through a massive collection of surface measurements.

When you combine these measurements, we arrive at a geodetic datum. Horizontal datums precisely specify each location on Earth’s surface in latitude and longitude or other coordinate systems. For instance, NAD83, WGS84 and ETRS89 are examples of geodetic datums.

Using geographic coordinate systems, we can define positions on Earth. For example, we use degrees/minutes/seconds (DMS) and decimal degrees (DD) for spherical coordinates. After we flatten the Earth with map projections, we use projected coordinate systems like the State Plane Coordinate System (SPCS) or UTM grid system.

How we project Earth to flatten map

In Tableau, the following geodetic datums (SRIDs) are supported:

  • NAD83 (EPSG:4269)
  • ETRS89 (EPSG:4258)
  • WGS84 (EPSG:4326)

The North American Datum of 1983 (NAD 83) is the most current datum being used in North America. It provides latitude and longitude and some height information using the reference ellipsoid GRS80. Geodetic datums like the North American Datum 1983 (NAD83) form the basis of coordinates of all horizontal positions for Canada and the United States.

In 1990 at it symposium in Firenze, Italy, EUREF designed the European Terrestrial Reference System 89 (ETRS89) in such a way that it would be based on the ITRS except that it is tied to the stable part of Europe, so that the relations between European stations are kept fixed. Coordinates in ETRS89 are expressed as either three dimensional (X, Y, Z) Cartesian coordinates or as three dimensional ellipsoidal coordinates (Φ, λ and H, Ellipsoidal height), based on the GRS80 ellipsoid.

The acceptance of ETRS89 by several communities (civil aviation, industry, national and regional agencies) as the backbone for geo-referencing in Europe is a continuous process. EuroControl (European Agency for Safely of Air navigation) uses ETRS89 since a long time in its technical specifications and there is a recommendation by the European Commission to adopt ETRS89 as the geodetic datum for geo-referenced information and to promote the use of ETRS89 within its member states.

The Global Positioning System uses the World Geodetic System (WGS84) as its reference coordinate system.

Deformation because of different map projection

In spatially enabled databases (such as IBM DB2, IBM Informix, Ingres, Microsoft SQL Server, MySQL, Oracle RDBMS, Teradata, PostGIS, SQL Anywhere and Vertica), SRIDs are used to uniquely identify the coordinate systems used to define columns of spatial data or individual spatial objects in a spatial column.

Remember: If you must show continent-wide or worldwide data, then use WGS84, if you are lowering the level of information to a region or city, use a national projection.

To move data from Shapefiles to a database, we can use Open Source tools to do all that quickly!I would recommend to use ogr2ogr (https://gdal.org/programs/ogr2ogr.html) because the data may be in a projection used in your country and should be transferred to a projection that Tableau supports.

Using ogr2ogr is really easy:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=<YOUR DB SERVER>;Database=<YOUR DATABASE NAME>;Trusted_Connection=yes" "<YOUR SHAPEFILE NAME>" -a_srs "EPSG:<YOUR SHAPEFILE SRID>"

How to use ogr2ogr for changing the coordinate reference system from EPSG:4326 to EPSG:3857:

ogr2ogr -s_srs EPSG:4326 -t_srs EPSG:3857 -f GPKG output.gpkg input.gpkg

If the data is stored in Database in WGS84 or ETRS89 but you have Geometry and you need Geography, we can use the following trick:

We must add 2 fields to the tables we want to convert:

We need Geometry and Geography fields:
FGeometry as Geometry and Geom2 as Geography;

1) Step one for transformation Geometry
SELECT d.OID, d.Geometry,
CONVERT(varbinary(max), d.Geometry, 1) as ConvertedGeometryBin,
(cast(CONVERT(varbinary(max), d.Geometry, 1) as geometry)) as FGeometry
FROM MyTable d;

2) Update our table with new values for Geometry
SET FGeometry = (cast(CONVERT(varbinary(max), Geometry, 1) as geometry));

3) Update our table with Geography data
SET Geom2 = FGeometry.STAsText();

4) Select data to see difference between Geography and Geometry data
SELECT d.OID, d.FGeometry, d.Geom2.STAsText(), d.FGeometry.STSrid
FROM MyTable d;

5) Copy desired columns into new table with Geography as Geometry
INSERT INTO table_new (geog) -- add fields to copy
SELECT GEOGRAPHY::STGeomFromWKB(table_old.geom.STAsBinary(), 4326)
FROM table_old;

Once you transfer Shapefile to DB, you can really do quality analysis over the data.

Although databases allow points, lines and areas to be in the same table, it is preferable that each element goes to its own table in the database.

Water distribution network
Highly experienced Manager, IT Consultant and BI Expert with a demonstrated history of providing high-end solutions for clients. A strong business development professional skilled in IT, Project Management, BI, Business Planning and Data Analysis. I have 28 years of work experience, with 24 years of management experience, including a senior-level position and 16 years of experience with OLAP and BI. 20 years of experience with the Public sector as Consultant, Project Manager, Sales, and Marketing Manager. Ready to help you when need the knowledge of BI, Business Analytics or GIS.Skills: Tableau, Power BI, MS SQL, R, Business Analysis, Data Analytics, MDM, Project Management, GIS, Spatial databaseThe main area of my work is R&D analytics and MDM for local and regional government data with data from companies in their ownership, which will create full business analysis, and how they spend money. The second area of interest is BI and data analytics for banks and insurance companies. Integration between GIS data and CRM data is another interesting area for me. Successful implemented Project / Product Management in all tasks that includes a description of my job, including contact with end-users, product/project marketing, product/project selling, design and developed WEB-GIS SaaS solutions for gas distributors and urban planning.Three years of intensive work on the analysis of economic data municipalities and cities, and lead seminars geomarketing for banks, insurers, wholesalers, and analysis of business results using big data and spatial data (including Risk Management).What I'm particularly proud of: I am the author of the GIS application and the first spatial database on the QNX OS and ZIM database. The application was written in C using the GKS graphics library, and an adaptation was made in the ZIM database to store simple geometric shapes: point, line and area. The app also had a successful installation with users. That was back in 1990, 10 years before Oracle introduced the Spatial database! I am the author of the first automated drug market analysis system. Sales data were collected from pharmacies, merged with wholesalers that marketed them, and linked to pharmaceutical companies to obtain market share by product and compare quantities and prices. I created a solution for accepting and validating data, creating and sending data in DW, and creating and filling Olap cubes. It was 2003.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.