Managing spatial data in HFSQL databases

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / Managing databases / HFSQL / Spatial data management

Overview
Spatial data
The different types of data
Remarks
Managing spatial data in the analysis
Managing spatial indexes
Importing and exporting spatial data
Getting data
Export spatial data
Manipulating spatial data in WLanguage
Displaying geographic data on a Map control

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Managing spatial data in HFSQL databases

Overview

Starting with version 2024, you can store and manipulate geometric and geographic data in HFSQL databases.
Geometric data refers to geometries defined in a two-dimensional surface, using X and Y coordinates.
Geographic data refers to geometries defined around a sphere (often representing the Earth), using latitude and longitude.
In both cases, the possible geometries are points, linestrings, multilinestrings and polygons.

Spatial data can be used to store and represent points of interest, roads and parcels in HFSQL data files. This makes it possible to perform numerous operations on spatial data, e.g., calculate areas, find an element in a given area, etc.

Remarks:

The OGC Simple Features standard is used.
Spatial data is compatible with the OpenGIS standard.

Spatial data

The different types of data

Two main types of data can be represented:

2-dimensional data using "geometric" types,
geographic data using "geographic" types.

The same objects, known as "geometries", can be used in both types of data. WLanguage offers specific variable types for each geometry:

Geometry	Geometry type	Geography type

Point	Point2D	PointGeo
Linestring	Linestring2D	LinestringGeo
Polygon	Polygon2D	PolygonGeo
Multipoint	MultiPoint2D	MultiPointGeo
Multilinestring	MultiLinestring2D	MultiLinestringGeo
Multipolygon	MultiPolygon2D	MultiPolygonGeo

The "2D" and "Geo" suffixes differentiate the two types of data. These "2D" and "Geo" types are mutually incompatible: it is impossible to calculate the distance between a geometric point and a geographic point, since they exist in different spaces.

A specific "Geometry" type allows you to store any type of geometry, whether 2D or geographic.

Remarks

The definition of different geometries requires mathematical rigor. For example, to be valid, a polygon must close back on itself. This means that the last point of the polygon must be the same as the first point. This point therefore appears twice in the definition of the polygon.
Data validity: To be saved in HFSQL data files, data must be valid. You can check data validity and fix potential issues using WLanguage functions.
Geographical approximation: Geographical calculations are performed using an approximation of the Earth's radius. This approximation is known as the Earth ellipsoid.

Managing spatial data in the analysis

Three types of items allow you to store spatial data in HFSQL data files:

Geometric data.
Geographic data.
Any type of geometric or geographic data.

"Geometric data (x,y)" items are used to store data mapped on a two-dimensional surface. In a "Geometric data" item, you can store data corresponding to one of the geometric variable types. This item type can be defined as "Spatial key".

"Geographic data (lat., long.)" items are used to store geographic data mapped on a spherical representation of the earth. In a "Geographic data" item, you can store data corresponding to one of the geographic variable types. This item type can be defined as "Spatial key".

"Any type of geometric or geographic data" items are used to store both types of data, i.e. geographic and geometric. Data is stored as "Geometry (2D or geographic coordinates)". This item type cannot be defined as "Spatial key".

Data files containing spatial data can then be manipulated using standard WLanguage functions (add, iteration, etc.). You can read and write to spatial items using the corresponding WLanguage types.

Managing spatial indexes

Spatial indexes make it easier to navigate through files containing spatial data. A spatial item is indexed if it is defined as "Spatial key".

Starting with version 2024 Update 2, you can identify the items that have been defined as "spatial keys" using HListSpatialKeys.

In a data file, you can apply a filter on spatial data and loop through the data file. It is recommended to use the following syntax to apply a filter on spatial data:

FOR EACH File WHERE WLanguage_function(File.Spatial_item, Spatial_variable)
// Process
END

where:

WLanguage_function is the function used to filter data.
Spatial_item is a spatial item in the data file. This item must:
- be of type "Geographic data" or "Geometric data".
- be defined as a "Spatial key".
Spatial_variable is a given spatial variable, used for comparison with the spatial item. This variable can be of type PolygonGeo, Polygon2D, etc.

Note Although available, the "FOR ANY FILE" syntax is not recommended, as it doesn't use spatial indexes and therefore doesn't optimize performance. In this case, the entire data file is looped through and spatial indexes are not used.

FOR EACH File
IF WLanguage_function(File.Spatial_item, Spatial_variable) = True THEN
...
END
END

Example of optimized iteration:

// Define a triangle delimiting a geographic area
// Store this triangle in a variable of type PolygonGeo
polyGeo is PolygonGeo
polyGeo.Outline.AddPoint(0n48.8187479172765, 0n1.9550104465229536)
polyGeo.Outline.AddPoint(0n48.685649220185574, 0n2.023674998054354)
polyGeo.Outline.AddPoint(0n48.82612543243871, 0n2.2106580854197566)
polyGeo = GeometryCorrect(polyGeo)
// Search for municipalities whose territory lies entirely within the specified triangle
NumberOfRecords is int
FOR EACH MunicipalityData where GeometryContain(MunicipalityData.geometry, polyGeo) 
	// Name of municipality in a trace window
	Trace(MunicipalityData.MunicipalityName)
END

Importing and exporting spatial data

Getting data

There are several methods to get spatial data:

A wide range of websites offer spatial data related to a given subject. Government websites (data.gov, etc.) contain datasets for land registry, protected natural areas, roads and rivers, etc. These datasets are often available in GeoJSON format. You can directly manipulate this format using WLanguage JSON functions, and convert the data to geometry objects to be integrated into HFSQL data files.
You can also import spatial data in WKT format.

Export spatial data

You can export spatial data stored in HFSQL data files to DSV or WKT using GeometrySerializeDSV and GeometrySerializeWKT.

Manipulating spatial data in WLanguage

WLanguage offers a large number of functions to manipulate spatial data. Most of these functions can be used with both geometric and geographic data. For more details, see Spatial data functions.

You can also manipulate spatial data using specific SQL functions. For more details, see SQL functions for handling spatial data.

Displaying geographic data on a Map control

Geographic data can be displayed on a Map control. To do so, it is necessary to convert each geometric polygon into a polygon that can be displayed on a Map control (MapPolygon variable).

Example of code:

PROCEDURE PolygonGeoToMapPolygon(polyGeo is PolygonGeo)

PolygonGeoForMapControl is MapPolygon

FirstPosition is geoPosition	// to store the first point
CurrentPosition is geoPosition	// to store the current point

// Iterate over the geographic points of the polygon
FOR i = 1 _TO_ polyGeo.Outline.Point..Count

	IF i = 1 THEN
		// Store first point 
		FirstPosition.Latitude = polyGeo.Ouline.Point[i].Latitude
		FirstPosition.Longitude = polyGeo.Outline.Point[i].Longitude
	END

	// Retrieve a geographic point
	CurrentPosition.Latitude = polyGeo.Ouline.Point[i].Latitude
	CurrentPosition.Longitude = polyGeo.Outline.Point[i].Longitude

	// Store point in the array of points making up the geometry
	// to draw it in the Map control
	Add(PolygonGeoForMapControl.Point, CurrentPosition)
END
RETURN PolygonGeoForMapControl