Skip to main content

GeoETL v0.6.0: ST_Area and ST_Length Spatial Functions

· 3 min read
Yogesh
Yogesh
GeoETL Maintainer

TL;DR: GeoETL v0.6.0 adds ST_Area and ST_Length spatial UDFs for calculating polygon areas and line lengths/perimeters in SQL queries.

What's New​

v0.5.0 introduced spatial distance calculations with ST_Distance. v0.6.0 expands the spatial SQL toolkit with area and length measurements.

New Functions:

FunctionDescription
ST_AreaCalculate area of polygon geometries
ST_LengthCalculate length of lines or perimeter of polygons

Both functions are powered by GEOS and work with all geometry input types: WKB binary, GeoArrow native types, and mixed geometry arrays from GeoJSON.

ST_Area: Calculate Polygon Areas​

ST_Area calculates the area of polygon geometries. It returns 0 for non-areal geometries like Points and LineStrings.

Example: Calculate Parcel Areas

geoetl-cli convert \
  -i parcels.geojson \
  -o areas.csv \
  --input-driver GeoJSON \
  --output-driver CSV \
  --sql "SELECT parcel_id, owner, ST_Area(geometry) as area_sqm
         FROM parcels
         ORDER BY area_sqm DESC"

Example: Filter by Minimum Area

geoetl-cli convert \
  -i buildings.geojson \
  -o large_buildings.geojson \
  --input-driver GeoJSON \
  --output-driver GeoJSON \
  --sql "SELECT * FROM buildings
         WHERE ST_Area(geometry) > 1000"

ST_Length: Calculate Lengths and Perimeters​

ST_Length calculates the length of LineString geometries or the perimeter of Polygons. Points return 0.

Example: Calculate Road Lengths

geoetl-cli convert \
  -i roads.geojson \
  -o road_lengths.csv \
  --input-driver GeoJSON \
  --output-driver CSV \
  --sql "SELECT road_name, road_type, ST_Length(geometry) as length_m
         FROM roads
         ORDER BY length_m DESC"

Example: Calculate Building Perimeters

geoetl-cli convert \
  -i buildings.geojson \
  -o perimeters.csv \
  --input-driver GeoJSON \
  --output-driver CSV \
  --sql "SELECT building_id, ST_Length(geometry) as perimeter
         FROM buildings"

Example: Filter Long Routes

geoetl-cli convert \
  -i hiking_trails.geojson \
  -o long_trails.geojson \
  --input-driver GeoJSON \
  --output-driver GeoJSON \
  --sql "SELECT name, difficulty, ST_Length(geometry) as length
         FROM hiking_trails
         WHERE ST_Length(geometry) > 10000"

Combining with ST_Distance​

You can combine the new functions with existing spatial UDFs for powerful queries:

geoetl-cli convert \
  -i parks.geojson \
  -o park_analysis.csv \
  --input-driver GeoJSON \
  --output-driver CSV \
  --sql "SELECT
           name,
           ST_Area(geometry) as area,
           ST_Length(geometry) as perimeter,
           ST_Distance(geometry, ST_Point(-122.4, 37.8)) as dist_from_sf
         FROM parks
         ORDER BY area DESC"

Complete Spatial SQL Toolkit​

GeoETL now provides 7 spatial SQL functions:

FunctionDescription
ST_Point / ST_MakePointCreate point from X, Y coordinates
ST_GeomFromTextParse WKT to geometry
ST_GeomFromWKBTag WKB binary as geometry
ST_DistanceMinimum distance between geometries
ST_AreaArea of polygon geometries
ST_LengthLength/perimeter of geometries

What's Next​

v0.7.0: ST_Buffer, ST_Contains, ST_Intersects for spatial predicates and buffering.

See the Roadmap.