4

I am trying to run a query in PostGres on a multiline spatial feature class table. Among other things, I am trying to use PostGIS to get the shape length of the reprojected geometry. My query looks like this:

SELECT  7 as region, reach_id, study_id, 
        case_no, co_fips, cid, fld_zone,
        validation_status, status_type, miles, 
        ST_Length(ST_Transform(geom,102008)) * 0.000621371 new_miles, 
        status_date, study_type, tier, line_type, 
        bs_zone, bs_stdytyp, bs_prelim_date, bs_lfd_date, geom
FROM data.studies_ln_reg_07

I have run this on multiple similar tables without issue but on this table I get the following error - tranform: latitude or longitude exceeded limits(-14). How can I overcome this issue?

6
  • 2
    Which SRID is your data in? Do SELECT ST_SRID(geom) from table. Also worth running ST_ISVALID(geom).
    – Encomium
    Sep 9 at 18:41
  • 4269 - NAD83 is the original
    – gwydion93
    Sep 9 at 18:45
  • 1
    Ok. What happens if you try this "SELECT st_transform(st_makevalid(st_setsrid(geom,4269)),102008)" ?
    – Encomium
    Sep 9 at 18:48
  • Look like I am getting the error - transform: latitude or longitude exceeded limits (-14).
    – gwydion93
    Sep 9 at 18:55
  • 2
    Then some of your points are incorrect or not in 4269. You might have to delete them or edit them manually after filtering them out using @JGH's query.
    – Encomium
    Sep 9 at 18:58
8

Some of your data is not in 4269 as expected. Maybe it has already been converted to 102008 or else.

You can query all records that are out of bounds:

select * from data.studies_ln_reg_07
where abs(st_x(geom)) > 180 or abs(st_y(geom)>90;

example:

select st_transform(st_setsrid('point(575 90)'::geometry,4269),102008);
ERROR:  transform: latitude or longitude exceeded limits (-14)
5
  • This looks like the right solution but these are lines not points. I am gettting the error: Argument to ST_X() must have type POINT. How do I do this for lines?
    – gwydion93
    Sep 9 at 19:00
  • 1
    You can use st_Xmin and st_Ymin (and if your true coordinates are near +-180 / +-90, you can also use st_Xmax but that's likely overkilled
    – JGH
    Sep 9 at 19:03
  • OK, min didn't return anything but max returned 4 rows. Would I just UPDATE the table by running ST_Transform(geom, 4269) on everything (to insure this doesn't happen again) or just those few rows where the reach_id matches the identified bad actors?
    – gwydion93
    Sep 9 at 19:08
  • 2
    Only you can know... it is bad data to be deleted? data in the wrong projection to be fixed (st_setSRID would be helpful), else? For the "ensuring it doesn't happen again", you can add constraints to your table to ensure the data that gets in is within the expected bounds
    – JGH
    Sep 9 at 19:13
  • Its a weird situation; if I run this on one of the bad items: SELECT ST_SRID(geom) FROM cnms.studies_ln_reg_07 WHERE reach_id = '291730100023'; it returns 4269. I used to run repair geometry on these in ArcMap before running a Python script, but I will have to think about how to control for this bringing in the raw data into PostGres. Thanks for the assistance.
    – gwydion93
    Sep 9 at 19:19

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.