Understanding OSM Geometry and SRIDs in PostGIS: A Guide to Transforming Coordinates

Understanding Geometry in PostGIS and SRID Transformations

Geometry data in PostGIS is stored using a spatial reference system (SRS) that defines the coordinates’ order and unit of measurement. In this case, we are dealing with OSM (OpenStreetMap) data, which typically uses the WGS84 SRS (World Geodetic System 1984).

However, when importing OSM data into PostGIS, it’s common to see SRIDs (Spatial Reference Identifiers) that correspond to different coordinate systems. The SRID serves as a unique identifier for each spatial reference system.

In this answer, we will explore how to transform the geometry in PostGIS from one SRID to another, specifically transforming OSM data from the WGS84 SRS to the EPSG:900913 SRS.

Understanding OSM Geometry and SRIDs

OSSM data uses a different ordering of coordinates than traditional geographic data. By default, OSM uses (lat, lon) as its coordinate pair, whereas PostGIS by default uses (lon, lat).

Furthermore, when importing OSM data into PostGIS, it’s common to see the original SRID associated with the data, which can be different from the PostGIS SRID used for storing the data.

Transforming Geometry in PostGIS

To transform geometry in PostGIS from one SRID to another, you can use the ST_Transform function. This function takes two arguments: the spatial reference system of the source geometry and the target SRID.

Here’s an example:

SELECT ST_astext(ST_transform(ST_SetSrid(ST_Point(11.573829,48.137969), 4326), 900913));

In this example, we create a ST_Point object with coordinates (11.573829, 48.137969) and set its SRID to 4326 (the WGS84 SRS). We then use the ST_transform function to transform this point to the EPSG:900913 SRS.

Correcting SQL Queries

To correct your original SQL query that uses ST_DWithin, you need to apply the same transformation to the way geometry:

SELECT name, shop 
FROM planet_osm_point
WHERE ST_DWithin(way,st_transform(st_setrid(st_point(11.573829,48.137969), 4326), 900913), 100);

By applying this transformation, you ensure that the ST_DWithin function uses the correct SRID for the way geometry.

Conclusion

Transforming geometry in PostGIS from one SRID to another is essential when working with OSM data. By using the ST_Transform function and applying the same transformation to both the source and target geometries, you can ensure that your queries produce accurate results.

Remember to always check the SRIDs associated with your data and apply the correct transformations to ensure compatibility between different spatial reference systems.

Last modified on 2024-01-07