Support for geospatial projections

[andrew8er]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[x] 0.2.6

In our DB (PostgreSQL) we have a column that stores point geometries with SRID 3857 (Web-Mercator):

import { Point } from 'geojson';

@Entity('gps_data')
export class GpsData {
	@Column('geometry', { spatialFeatureType: 'Point' ,  srid: 3857})
	geom!: Point;
}

The current implementation sets the SRID of the GeoJSON data to whichever SRID the column was specified with:

// InsertQueryBuilder.ts line 468
expression +=  `ST_SetSRID(ST_GeomFromGeoJSON(${this.connection.driver.createParameter(paramName, parametersCount)}), ${column.srid})::${column.type}`

allthough GeoJSON by definition always uses WGS84 (https://tools.ietf.org/html/rfc7946#section-4), and support for different reference systems was removed (https://tools.ietf.org/html/rfc7946#appendix-B.1).

So technically, the current implementation is not really correct for columns not using WGS84, like in my use case.

A fix would be using something like this:

expression +=  `ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(${this.connection.driver.createParameter(paramName, parametersCount)}), 4326))), ${column.srid})::${column.type}`

But this might be a breaking change for some users, so it is not really an option.

Are there already any thoughts on how to handle this situation? Maybe in conjunction with pluggable runtime data representations (I'm thinking of WKT or e.g. [lon: number, lat: number] for point geometries)?

Specifically asking @mojodna, since you implemented the spatial types support for PostgreSQL.

[mojodna]

@andrew8er that's a really good point.

I guess I was thinking that the internal representation is "GeoJSON" (a sensible encoding of feature type + coordinates that interops well and is understood by both PostGIS + JS/TS) rather than GeoJSON (the strict interpretation), meaning that the representation one works with in code matches the coordinate system stored in the database (allowing for JS calculations using the local coordinate system; this probably makes more sense if you're using UTM rather than Web Mercator, but that's a matter of perspective).

In other words, I think forcing JS/TS code to work in WGS84 (per the GeoJSON spec) would be a bad idea, so I'm in favor of keeping the status quo.

[andrew8er]

I see. I did not mean to change the current behavior, but I think it should be clarified in the documentation. I will think about how to approach this problem. In the meantime, feel free to close this issue or keep it open for other people to discuss.

[UrielCh]

Is there a there a code sample using WGS84 collumn ?

[mojodna]

@UrielCh

@Column("geometry", {
  featureType: "Point",
  srid: 4326 // https://spatialreference.org/ref/epsg/wgs-84/
})