transform function returning null?

[Robert Schwartz]

Hi,

I'm trying to convert some WGS 84 (srid 4326) lat/long data into UTM Zone 18N (srid 32618) to do some Distance() calcs on them, and I can't seem to do it. I've tracked the problem down to the following Transform returning NULL:

Transform(MakePoint(45.26621, -75.94499, 4326), 32618)

Any ideas on what I'm doing wrong??

FYI - I'm using spatialite-gui 1.4

Thanks in advance for any help on this!

-Rob

[smellyspice]

On Jun 13, 5:40 pm, Robert Schwartz <smellysp...@gmail.com> wrote:
> Hi,
>
> I'm trying to convert some WGS 84 (srid 4326) lat/long data into UTM Zone
> 18N (srid 32618) to do some Distance() calcs on them, and I can't seem to do
> it. I've tracked the problem down to the following Transform returning NULL:
>
> Transform(MakePoint(45.26621, -75.94499, 4326), 32618)
>

Hmm.. answering my own post, it appears as though if I reverse Lat and
Long, I now get a result instead of NULL.

I've seen this "role reversal" before, when I tried to open a
spatialite db in Q-GIS. Instead of seeing my 3 waypoints in a (near)
horizontal line, they were vertical. What have I done wrong? Something
in my table scheme or should I have inserted my data long before lat??
Confused. The docs all type about Point types being X, Y..... Is X not
Lat and Y not Long?

Thanks,

-Rob

[James Card]

On Mon, 13 Jun 2011 15:15:11 -0700, smellyspice <smell...@gmail.com>
wrote:

> Is X not Lat and Y not Long?

No, you'll need to reverse that.

--
James Card -- <http://jdcard.com/>
Often there are several ways to understand a given set of
facts; some of them may be more useful than others.

[Brian Sorahan]

I'm just going to reply on this thread since the email I want to send would have the same title.

However, I think I have a very different situation from the OP.

I am trying to build a Go wrapper around spatialite. https://github.com/briansorahan/spatialite

The good news is that it (mostly) works with very little code thanks to the fact that the sqlite3 driver for Go provides an easy way to load extensions.

This is the meat of my package so far https://github.com/briansorahan/spatialite/blob/1c12470b476639309c404340f143678d2cf141a6/driver.go

The trouble I'm having is with the Transform function. The test I wrote uses the following query: https://github.com/briansorahan/spatialite/blob/1c12470b476639309c404340f143678d2cf141a6/driver_test.go#L48

I'm testing everything in a docker container based on this Dockerfile, which shows all the steps I'm using to install dependencies on alpine linux.

The OP's query with long, lat actually works for me:

spatialite> SELECT AsGeoJSON(ST_Transform(MakePoint(-75.94499, 45.26621, 4326), 32618));
{"type":"Point","coordinates":[425866.0552741262,5012957.964198887]}

However I'm having trouble with GeoJSON (which is what I tried using in my Go test, and also exhibits problems using spatialite-tools):

spatialite> .nullvalue NULL
spatialite> SELECT AsGeoJSON(ST_Transform(GeomFromGeoJSON('{"type":"Point","coordinates":[-75.94499,45.26621]}'), 32618));
NULL 

What could the problem with this query be?

I've made sure to call InitSpatialMetaData https://github.com/briansorahan/spatialite/blob/1c12470b476639309c404340f143678d2cf141a6/driver.go#L39

I've also tried loading the epsg table that I found through the tutorial, with no success https://github.com/briansorahan/spatialite/blob/1c12470b476639309c404340f143678d2cf141a6/epsg-sqlite.sql

Any help/advice is greatly appreciated!

[a.fu...@lqt.it]

On Fri, 28 Apr 2017 13:43:52 -0700 (PDT), Brian Sorahan wrote:
>> spatialite> .nullvalue NULL
>> spatialite> SELECT
>>
>
> AsGeoJSON(ST_Transform(GeomFromGeoJSON('{"type":"Point","coordinates":[-75.94499,45.26621]}'),
>> 32618));
>> NULL 
>
> What could the problem with this query be?
>

Hi Brian,

your SQL query is malformed and can never work in any way.

you are asking ST_Transform() to reproject the input Geometry
into the srid=32618 [WGS 84 / UTM zone 18N].

but your input Geometry has an undefined SRID, as this
SQL query clearly shows:

SELECT ST_Srid(GeomFromGeoJSON(
'{"type":"Point","coordinates":[-75.94499,45.26621]}'));
--------
-1

consequently ST_Tranform() fails (it's unable to determine
the initial SRID) then returning a NULL value.


solution #1:
------------
SELECT ST_Transform(GeomFromGeoJSON('{"type":"Point",
"crs":{"type":"name","properties":{"name":"EPSG:4326"}},
"coordinates":[-75.94499,45.26621]}'), 32618);

you can explicitly define some appropriate SRID
within the GeoJSON string.


solution #2
-----------
SELECT ST_Transform(SetSrid(GeomFromGeoJSON('{"type":"Point",
"coordinates":[-75.94499,45.26621]}'), 4326), 32618);

alternatively you can call SetSRID() so to explicitly
set some SRID to the your input geometry.

bye Sandro

[a.fu...@lqt.it]

addendum:

it apparently seems that any GeoJSON geometry missing an
explicit CRS declaration should always be assumed to
intend SRID=4326 (WGS 84 long/lat).

http://geojson.org/geojson-spec.html#coordinate-reference-system-objects

"The default CRS is a geographic coordinate reference system, using the
WGS84 datum, and with longitude and latitude units of decimal degrees."

this is not the current interpretation implemented by
libspatialite: a GeoJSON geometry missing the CRS member
is directly assigned to SRID=-1 (undefined).

let me know your opinions about the correct interpretation
of the GeoJSON default CRS.

bye Sandro

[Brian Sorahan]

Hi sandro,

Thanks for your help! I did assume that WGS 84 would be implied if I didn't specify an SRID.

In fact, RFC 7946 removes the ability to specify a custom CRS.

I am mostly concerned that my installation is working properly, and using SetSRID has convinced me that it is.

[Jukka Rahkonen]

Hi,

In fact https://tools.ietf.org/html/rfc7946 does not remove the ability to specify a custom CRS. It just does not give any advice about how to do it:

However, where all involved parties have a prior arrangement, alternative coordinate
reference systems can be used without risk of data being misinterpreted.

I suppose that those who need to use other coordinate reference systems will continue to use the 2008 GeoJSON specification.

Perhaps GeomFromGeoJSON should use WGS 84 as default if CRS member is missing, but with on override option if user happens to know the correct CRS by some sort of prior arrangement

GeomFromGeoJSON( geoJSONGeometry String [, SRID Integer]) : Geometry

-Jukka Rahkonen-


-Jukka Rahkonen-