[postgis-users] trigger function with nest IF/THEN, . . . . problems . . .
All, Is this looking correct (triggered AFTER insert) How does one test a SELECT INTO statement BTW??, this gets stuck on trying to insert the same RECID over and over again (error at the bottom . . .) do the log_alert_out and log_alert_into values somehow need to be cleared manually? : $BODY$ DECLARE log_alert_out boolean; log_alert_into boolean; BEGIN IF (TG_OP = 'INSERT') THEN SELECT INTO -- First grab the DISJOINT values for each end of line segment . . . log_alert_out, log_alert_into ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom), ST_DisJoint(ST_StartPoint(T.the_line), fences.the_geom) from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) order by T.acqtime desc -- This should be the last record inserted. limit 1 ; IF (log_alert_out log_alert_into) THEN -- If points are not both IN or OUT of polygon . . . we only want crossings . . . INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) and (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc -- This should be the last record inserted. limit 1 ; RETURN new; END IF; END IF; END; $BODY$ LOG extract: 10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'. 10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint boundary_alerts_log_pkey Detail: Key (recid)=(4299314) already exists. Where: SQL statement INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) -- and --(ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) -- --ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc limit 1 PL/pgSQL function boundary_alerts line 25 at SQL statement . . . . . . . ## some stuff removed . . . . . . . . . . . 10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'. 10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint boundary_alerts_log_pkey Detail: Key (recid)=(4299314) already exists. Where: SQL statement INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) -- and --(ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) -- --ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc limit 1 PL/pgSQL function boundary_alerts line 25 at SQL statement 10:34:06 DbSvcX: INSERT INTO Loc(msgId,acqTime,can,esn,vName,lat,lon,course,alt,qual,vel,latPrv,lonPrv,coursePrv,velPrv,the_geom,the_geomPrv,the_line,msgType,msgIdRef,tName) VALUES (181210231034635,'2012-10-23 10:34:06',1850,'10202037','2687',44.9588,-93.1037,0.0,0.0,1,0.0,44.9587,-93.1037,0.0,0.0,'010120840D03002EE48CB7D0772141F734D5AC96AF0341','010120840D0300C345FCF7D0772141697B72FB72AE0341','010220840D03000200C345FCF7D0772141697B72FB72AE03412EE48CB7D0772141F734D5AC96AF0341',18,0,'NONE'); 10:34:06 DbSvc: Loc: Inserted. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Looking for example SQL code to generate linestring for GPS/Plow Up/Down action.
I have a (GPS Point) table that looks like: 1;0;0;0;0;geom 2;0;0;0;1;geom 3;0;0;0;0;geom 4;0;0;0;1;geom 5;0;0;0;0;geom 6;0;0;0;1;geom 7;0;0;0;0;geom 8;0;0;0;1;geom 9;0;0;0;0;geom 10;0;0;0;1;geom 11;0;0;0;1;geom 12;0;0;0;0;geom 13;0;0;0;0;geom 14;0;0;0;1;geom 15;0;0;0;0;geom 16;0;0;0;1;geom 17;0;0;0;0;geom The next to last column is the plow up/down switch. 1= Plow Down or start of line segment, and 0 = Plow up or end of line segment. If two or more sequential records have a 1 value, then it's to be treated as a multi-segment line. So, what would the SQL look like to do that. I know I need a self join, but the 1/0 value thing is making my brain freeze for some reason . . . the inner select needs to select the rows sequentially beginning from a 1 value, until it reaches a 0 value. This would become a linestring in the outer result, right? Thanks bobb ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work)
I'm getting an error in proj4text in 9.1.4 for our custom projection which works just fine in 8.4.2. when inserting a geom like: INSERT INTO InsertInfoStp(. . . . the_geom, . . . .) VALUES (. . . . ST_Transform (ST_SetSRID (ST_MakePoint (-93.1286,44.9675), 4326), 200068), . . . .) The problem is in the ST_Transform, if I drop it, it succeeds. Can anyone see anything obvious with : srid=200068 auth_name=EPSG auth_srid=200068 srtext=PROJCS[MN Ramsey County,GEOGCS[NAD83 (1986),DATUM[GRS 1980 + MN Ramsey Elevation,SPHEROID[Unknown,6378418.941,298.2572242549219]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453292519943295]], PROJECTION[Lambert_Conformal_Conic_2SP],PARAMETER[standard_parallel_1,45.13],PARAMETER[standard_parallel_2,44.88],PARAMETER[latitude_of_origin,44.79], PARAMETER[central_meridian,-93.383334],PARAMETER[false_easting,50],PARAMETER[false_northing,10],UNIT[Foot_US,0.30480060960121924]] proj4text=+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work)
Sorry, forgot to add the error: ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs': projection not named ** Error ** ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs': projection not named SQL state: XX000 From: postgis-users-boun...@postgis.refractions.net [postgis-users-boun...@postgis.refractions.net] on behalf of Basques, Bob (CI-StPaul) [bob.basq...@ci.stpaul.mn.us] Sent: Tuesday, June 26, 2012 9:51 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work) I'm getting an error in proj4text in 9.1.4 for our custom projection which works just fine in 8.4.2. when inserting a geom like: INSERT INTO InsertInfoStp(. . . . the_geom, . . . .) VALUES (. . . . ST_Transform (ST_SetSRID (ST_MakePoint (-93.1286,44.9675), 4326), 200068), . . . .) The problem is in the ST_Transform, if I drop it, it succeeds. Can anyone see anything obvious with : srid=200068 auth_name=EPSG auth_srid=200068 srtext=PROJCS[MN Ramsey County,GEOGCS[NAD83 (1986),DATUM[GRS 1980 + MN Ramsey Elevation,SPHEROID[Unknown,6378418.941,298.2572242549219]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453292519943295]], PROJECTION[Lambert_Conformal_Conic_2SP],PARAMETER[standard_parallel_1,45.13],PARAMETER[standard_parallel_2,44.88],PARAMETER[latitude_of_origin,44.79], PARAMETER[central_meridian,-93.383334],PARAMETER[false_easting,50],PARAMETER[false_northing,10],UNIT[Foot_US,0.30480060960121924]] proj4text=+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work)
Geez, I think I need to slow down some. I figured out it would probably be more pertinent to list the POSTGIS version instead of PostGres. proj4 parsing error in : POSTGIS=1.5.3 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.8.0, 6 March 2012 LIBXML=2.7.8 USE_STATS the INSERT works fine in: POSTGIS=1.4.1 GEOS=3.2.0-CAPI-1.6.0 PROJ=Rel. 4.7.1, 23 September 2009 USE_STATS I used the same creation statement in both DB's for the custom projection. bobb From: postgis-users-boun...@postgis.refractions.net [postgis-users-boun...@postgis.refractions.net] on behalf of Basques, Bob (CI-StPaul) [bob.basq...@ci.stpaul.mn.us] Sent: Tuesday, June 26, 2012 9:52 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work) Sorry, forgot to add the error: ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs': projection not named ** Error ** ERROR: AddToPROJ4SRSCache: couldn't parse proj4 string: '+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs': projection not named SQL state: XX000 From: postgis-users-boun...@postgis.refractions.net [postgis-users-boun...@postgis.refractions.net] on behalf of Basques, Bob (CI-StPaul) [bob.basq...@ci.stpaul.mn.us] Sent: Tuesday, June 26, 2012 9:51 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] proj4 error between versions 8.4.2 (works) and 9.1.4 (doesn't work) I'm getting an error in proj4text in 9.1.4 for our custom projection which works just fine in 8.4.2. when inserting a geom like: INSERT INTO InsertInfoStp(. . . . the_geom, . . . .) VALUES (. . . . ST_Transform (ST_SetSRID (ST_MakePoint (-93.1286,44.9675), 4326), 200068), . . . .) The problem is in the ST_Transform, if I drop it, it succeeds. Can anyone see anything obvious with : srid=200068 auth_name=EPSG auth_srid=200068 srtext=PROJCS[MN Ramsey County,GEOGCS[NAD83 (1986),DATUM[GRS 1980 + MN Ramsey Elevation,SPHEROID[Unknown,6378418.941,298.2572242549219]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453292519943295]], PROJECTION[Lambert_Conformal_Conic_2SP],PARAMETER[standard_parallel_1,45.13],PARAMETER[standard_parallel_2,44.88],PARAMETER[latitude_of_origin,44.79], PARAMETER[central_meridian,-93.383334],PARAMETER[false_easting,50],PARAMETER[false_northing,10],UNIT[Foot_US,0.30480060960121924]] proj4text=+proj=lcc +lat_2=44.88 +lat_1=45.13 +lat_0=44.79 +lon_0=-93.383334 +x_0=152400.3048 +y_0=30480.0610 +units=ft +to_meter=0.30480060960122 +a=6378418.941 +b=6357033.310 +no_defs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
