transform longitude and latitude to postgis geometry failed: exceeded limits(-14)

Question

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:

(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:

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

Answer 1

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;

Answer 2

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.