IT Insights

Tools for GIS Database Spatial Queries

Posted by Aditya Firake on November 2, 2017

Tools for GIS Database Spacial QueriesA Geographic Information System (GIS) is an application that creates, analyzes and displays location-based data. It assimilates various types of geographical information and then executes them in common database operations.

GIS applications have evolved to provide high-quality data and enable more holistic, enterprise-wide decision-making across multiple industries, including real estate and public health. As a result, knowledge of GIS management has been an increasingly sought-after skill.

This blog will discuss a few tools that are used alongside GIS including Shapefiles and writing PostGIS spatial queries.  

Shapefile and geodatabase (GDB)

Organizations can analyze the condition of the Earth's surface, atmosphere and subsurface by feeding satellite data into a GIS. They then can store this information in a particular file format such as a Shapefile or a geodatabase (GDB), etc.

Shapefile is an Environmental Systems Research Institute (ESRI) vector data storage format that uses non-topological formats for storing location, shape and geographic elements. The Shapefile stores data in different vector layers which easily can be accessed in the GIS.

The geometric attributes for a feature are stored as a particular shape consisting of vector coordinates. Shapefiles support lines, points and area features, where the area feature in Shapefile is represented as polygon-shaped closed loops.

Websites, including Zillow or Trulia that estimate home values, rents and other home-related information, offer such decisions by analyzing property locations based on GIS data. So, such organizations would need to extract information from Shapefiles and perform spatial operations using spatial queries to gain such analysis. These are no different from other database queries. Commonly used spatial functions include Distance, Equals, Contains, Intersects, Touches, Crosses, Overlaps, etc.

The GDB file offers a comprehensive model of the collection of various geographic datasets within the database. An ArcGIS geodatabase is an assemblage of the various geo-datasets held together in a common file system, such as in a folder in a Microsoft Access database, or a multiuser relational DBMS (such as Oracle, Microsoft SQL Server, PostgreSQL, Informix, or IBM DB2). Every file format, however, has its own benefits and shortcomings. It’s possible to convert GDB files into Shapefile formats using tools such as QGIS.

PostgreSQL

PostgreSQL is a database that can store GIS data. PostgreSQL is allowed by PostGIS (the extension) to store GIS data and many GIS functions to analyze and handle the data. The shp2pgsql data loader converts ESRI Shape files into SQL-appropriate files for insertion into a PostGIS/PostgreSQL database.

PostgreSQL can be connected to a QGIS application that supports viewing, editing and analysis of geospatial data. PostGIS functions can be used to complete full analysis in a similar manner.

However, they do have the following limitations:

  • No support for any compression files other than .zip archive
  • Numeric attributes are stored in character formats rather than in an ordinary binary format. For real numbers, this may lead to rounding errors.
  • File sizes must be less than 10MB
  • They cannot store both a date and time in a field
  • Poor supporters for Unicode characters and strings
  • Doesn’t allow field names longer than 10 characters

Developers can use PostGIS setup to complete a full analysis in a similar manner as other desktop GIS applications.

In most desktop GIS programs, specific analysis tools are used to create an output--usually a new Shapefile. The same thing can be done with PostGIS by utilizing the output of the query in creating a table. Developers can use generated output as input for the next GIS tool and then a series of Shapefiles throughout the analysis.

Business use case

In the below-mentioned PostGIS special query, we have used two tables, shpa_airport, which contain the list of airports and their corresponding geolocation, and shpa_county_boundary, which contains country names and their boundary limit details. These two tables were created in PostgreSQL by loading the corresponding shapefiles using PostGIS 2.0 Shapefile and DBF Loader Exporter. Finally, ST_Contains Returns 1 or 0 to indicate whether a geometry g1 completely contains geometry g2.

Spacial queries use case 

This PostGIS spatial query will provide airport names and the respective country name in which they are located.

This post has included information about Shapefiles, PostgreSQL and writing PostGIS spatial queries. Emtec’s Application Development services can help your organization use the tools you need to get the most of out of geographic data for business decisions. Contact us today!

Topics: .IT, PostGIS

Search the Blog

Get IT Insights in Your Inbox

More Emtec Insights