1

I'm trying to transform my longitude and latitude column in PostgreSQL, into PostGIS geometry point. (address in LA area)

This is the sample:(-118.373258481016618, 34.712161771785425) // longitude, latitude

When I run this Command:

 SET geometry = 
 ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 
 2227);

I would have this Terminal Error :

Executing (default): UPDATE myTable SET geometry = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 2227); Unhandled rejection SequelizeDatabaseError: transform: couldn't project point (-118.001 -1.4873e+008 0): latitude or longitude exceeded limits (-14)

I know the command works because, if I hard code in latitude like this:

UPDATE crime_la_counties SET geometry = 
ST_Transform(ST_SetSRID(ST_MakePoint(longitude, 34.095360134448367), 4326), 
2227);

It's sort of working, and I can see my points in QGIS like this: enter image description here

(It's a line because I hard coded in latitude).

Anyone knows what went wrong with latitude column? The column type currently is numeric. I also tried to trunc + round latitude:

SELECT trunc( CAST("latitude" as numeric), 8) FROM myTable;

SELECT round( CAST("latitude" as numeric), 8) FROM myTable;

It didn't work either...

version:

QGIS 2.3.3

PG Admin4

psql (PostgreSQL) 9.6.5

UPDATE:

After I type in:

select min(latitude), max(latitude) from crime_la_counties

I got this:

enter image description here

so trying to filter the raw data now... in progress

  • Are you also including quotations in longitude and latitude ? – Ahsan Mukhtar Sep 12 '17 at 4:49
  • Thank you for reply!! No, I had just numeric numbers for both longitude and latitude – Sonrisa Sep 12 '17 at 4:54
  • try it by changing to double precision – Ahsan Mukhtar Sep 12 '17 at 5:25
  • can you share a screenshot of values in your table ? – Ahsan Mukhtar Sep 12 '17 at 5:26
  • 1
    what does this show: select min(latitude), max(latitude) from mytable; – Mike T Sep 12 '17 at 5:39
2

The problem was solved by doing : select min(latitude), max(latitude) from mytable; according to – Mike T 's anwer.

We found multiple data in latitude that were random negative numbers. So the solution was using conditional DELETE:

 DELETE FROM myTable WHERE latitude < -90 ::DECIMAL;
  • 1
    Instead of adding Solved to your question, please mark your self-answer as accepted: gis.stackexchange.com/help/accepted-answer – AndreJ Sep 12 '17 at 8:47
  • It's good to know that you have solved. Please mark it as answered. – Ahsan Mukhtar Sep 12 '17 at 9:24
  • I'm so sorry! I thought I have to wait for 48 hours to accept my own answer? I wanted to accept Mike T's "answer" but his answer was only a comment... I will accept my answer ASAP! and thank you for everything! – Sonrisa Sep 12 '17 at 17:38
1

A few issues,

  1. There is a type mismatch. In the code you say ST_MakePoint(longitude, latitude), but in the code you provide you show that long,lat are in text. You can tell this because they're in quotes. You're not showing us the code you're actually running. There isn't even a valid ST_MakePoint(text,text), but only for ST_MakePoint(double precision, double precision)
  2. The error generated isn't truncation, it's because the range is invalid and your lat/long are switched.

Observe,

SELECT ST_AsText(pt)
FROM ( VALUES
        ('-118.373258481016618', '34.712161771785425')
) AS t(long,lat)
CROSS JOIN LATERAL                                                                                                          
        ST_Transform(ST_SetSRID(ST_MakePoint(lat::double precision, long::double precision), 4326), 2227) AS pt;
ERROR:  transform: couldn't project point (34.7122 -118.373 0): latitude or longitude exceeded limits (-14)

The above has your error, lets switch lat/long,

test=# SELECT ST_AsText(pt)
FROM ( VALUES
        ('-118.373258481016618', '34.712161771785425')
) AS t(long,lat)
CROSS JOIN LATERAL  ST_Transform(ST_SetSRID(ST_MakePoint(long::double precision, lat::double precision), 4326), 2227) AS pt;
                st_astext                
-----------------------------------------
 POINT(7201634.6972664 996460.680138602)
(1 row)

As a side note the range, as mentioned here for SRID 2227 is 36.7400-38.7000 So your 34.7121 is not likely to be useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.