search

notum-cz / strapi-plugin-location

This plugin allows users to create location inputs and store latitude and longitude values as geometry types in a PostGIS database. It also provides functionality to filter items based on their location.
MIT License
24 stars 13 forks source link

invalid input syntax for type double precision: ""13.537340924817212"" #94

Closed JannikZed closed 10 months ago

JannikZed commented 10 months ago

Bug report

Describe the bug

After testing out your v1.2.0 I receive now the following error when trying to start: 

error: 
          UPDATE cities
          SET ch_koordinate_geom = ST_SetSRID(ST_MakePoint(
              CAST((ch_koordinate::json->'lng')::text AS DOUBLE PRECISION),
              CAST((ch_koordinate::json->'lat')::text AS DOUBLE PRECISION)

          ), 4326)
          WHERE (ch_koordinate::json->'lng')::text != 'null' AND
                (ch_koordinate::json->'lat')::text != 'null'
           - invalid input syntax for type double precision: ""13.537340924817212""
omikulcik commented 10 months ago

Hi @JannikZed, can you share an example of what is in your database? At first glance it seems that you have the coordinates stored as a string (inside the jsonb) which should not be the case. In my db I have a column location which has {"lat": 49.19785878427575, "lng": 16.66385650634766} (no strings around the numbers) and a column location_geom where the data is stored like this POINT (16.66385650634766 49.19785878427575)

Without more information it is very hard to replicate the bug.

JannikZed commented 10 months ago

you are right - seems like we have some lat and lng fields formatted as strings. Don't know, how that did happen .. that is the problem with that issue.

twog93 commented 2 months ago

Hi, got the same issue,

using strapi 4.15.5 Postgresql 14 Postgis 3.1.4

value of column location :

{ "lat": "43.580601349999995", "lng": "3.9403721111681365" }

Error from strapi log

: UPDATE evenements │ │ SET address_geom = ST_SetSRID(ST_MakePoint( │ │ CAST((address::json->'lng')::text AS DOUBLE PRECISION), │ │ CAST((address::json->'lat')::text AS DOUBLE PRECISION) │ │ │ │ ), 4326) │ │ WHERE (address::json->'lng')::text != 'null' AND │ │ (address::json->'lat')::text != 'null' AND │ │ address_geom IS NULL; │ │ - syntaxe en entrée invalide pour le type double precision : « "3.9403721111681365" »