[postgis-users] Spatial column from text via a VIEW, Possible?
All, First posting, been looking for a while now, maybe not possible, or I'm not asking/searching with correct vocabulary. I have a table with records like this that are fed from a Vendor source: ID STAT RXTIME CMD 165 12012-03-05 08:14:09.095626 >Plot:11072940,2012-02-29 05:33:03.0,44.9677,-93.1268,4.10,0,Good; via a VIEW I have it looking like this: RXTIME CMD_TYPE ESN TIME_IDX LAT LON SPEED EVENT GOOD 2012-03-05 08:14:09.095626 Plot 11072940 2012-02-29 05:33:03.0 44.9677 -93.1268 4.10 0 Good Question, how can I (if possible) convert the LAT/LON fields into a Spatial column in the/a VIEW from the TEXT fields. My (future) VIEW creation SQL so far: select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; thanks bobb ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
My (future) VIEW creation SQL so far: select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research."SpatialDB Advice and Design, Solutions Architecture and Programming,Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified ProfessionalOracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397Mobile: +61 418 396391Skype: sggreenerLongitude: 147.20515 (147° 12' 18" E)Latitude: -43.01530 (43° 00' 55" S)GeoHash: r22em9r98wgNAC:W80CK 7SWP3___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Hi, Simon, Cool, almost there, I swear I tried something similar to that this afternoon . . . Ok, this is good, that got it further, now I need to change the data type, from TEXT to NUMERIC, . . . . ERROR: column "lon" does not exist LINE 7:ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4... ^ >>> "Simon Greener" wrote: ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom, \ My (future) VIEW creation SQL so far: select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Ha, success!!! Coll, Thanks Simon (A LOT !!) select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Bob,Yeah, sorry, didn't spot the aliasing of part3 and part4 but you got it anyway.Can PostgreSQL/PostGIS let you now create a function based index over the computed geometry column?SOn Tue, 06 Mar 2012 10:28:55 +1100, Bob Basques wrote: Ha, success!!! Coll, Thanks Simon (A LOT !!) select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research."SpatialDB Advice and Design, Solutions Architecture and Programming,Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified ProfessionalOracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397Mobile: +61 418 396391Skype: sggreenerLongitude: 147.20515 (147° 12' 18" E)Latitude: -43.01530 (43° 00' 55" S)GeoHash: r22em9r98wgNAC:W80CK 7SWP3___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
That was the next question . . . . :c) Out for the day, I'll be back at this tomorrow though . . . Thanks for the help too. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
All, made nice progress from Simon's suggestions, but . . . Ok, I'm probably trying to push this harder than I need to, but, I'm trying to ST_Transform the data from 4326 to 200068 (Our private projection) in the construction of the view, but I keep hitting permission errors. NOTE: we use the 200068 projection for all of our data, have for years. It's definition in SPATIAL_REF_SYS is correct. This works fine: select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; But this give me an error, do I have the ST_Transform in the right spot, is 8.4.2 too old of a release? : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)), 200068) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; Like: ERROR: function st_transform(geometry) does not exist LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** Error ** ERROR: function st_transform(geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 212 Thanks bobb ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Ok, it looks like I may not have PROJ support compiled in, but wouldn't the existence of the SPATIAL_REF_SYS mean that PROJ is installed?? bobb >>> "Bob Basques" wrote: All, made nice progress from Simon's suggestions, but . . . Ok, I'm probably trying to push this harder than I need to, but, I'm trying to ST_Transform the data from 4326 to 200068 (Our private projection) in the construction of the view, but I keep hitting permission errors. NOTE: we use the 200068 projection for all of our data, have for years. It's definition in SPATIAL_REF_SYS is correct. This works fine: select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; But this give me an error, do I have the ST_Transform in the right spot, is 8.4.2 too old of a release? : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)), 200068) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; Like: ERROR: function st_transform(geometry) does not exist LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** Error ** ERROR: function st_transform(geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 212 Thanks bobb ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
I dont think so Bob.
Check this out:
In this case the table still exists
create table srid_inexistent_test(
> id serial not null);
> select * from
> addgeometrycolumn('public','srid_inexistent_test','the_geom',99,'POINT',2);
> NOTA: CREATE TABLE criará sequência implícita
> "srid_inexistent_test_id_seq" para coluna serial "srid_inexistent_test.id"
> ERRO: AddGeometryColumns() - invalid SRID
> CONTEXT: SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
> $5 , $6 )"
> função PL/pgSQL "addgeometrycolumn" linha 4 em comando SQL
> ** Erro **
> ERRO: AddGeometryColumns() -* invalid SRID
> *SQL state: P0001
> Contexto: SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
> $5 , $6 )"
> função PL/pgSQL "addgeometrycolumn" linha 4 em comando SQL
In this particular table I've dropped it:
drop table spatial_ref_sys;
> create table srid_inexistent_test(
> id serial not null);
> select * from
> addgeometrycolumn('public','srid_inexistent_test','the_geom',99,'POINT',2);
> ERRO: relação "spatial_ref_sys" não existe
> LINE 1: SELECT SRID FROM spatial_ref_sys WHERE SRID = $1
> ^
> QUERY: SELECT SRID FROM spatial_ref_sys WHERE SRID = $1
> CONTEXT: PL/pgSQL function "addgeometrycolumn" line 74 at comando SQL
> comando SQL "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 , $5 , $6 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at comando SQL
> ** Erro **
> ERRO: relação "spatial_ref_sys" não existe
> SQL state: 42P01
> Contexto: PL/pgSQL function "addgeometrycolumn" line 74 at comando SQL
> comando SQL "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 , $5 , $6 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at comando SQL
PostGIS uses spatial_ref_sys table to verify, when you create a geometry
table, if the projection you're using is available.
If I've made any mistakes, experts, please correct me :D
George
On Tue, Mar 6, 2012 at 3:32 PM, Bob Basques wrote:
> Ok, it looks like I may not have PROJ support compiled in, but wouldn't
> the existence of the SPATIAL_REF_SYS mean that PROJ is installed??
>
> bobb
>
>
>
> >>> "Bob Basques" wrote:
>
> All,
>
>
> made nice progress from Simon's suggestions, but . . .
>
>
> Ok, I'm probably trying to push this harder than I need to, but, I'm
> trying to ST_Transform the data from 4326 to 200068 (Our private
> projection) in the construction of the view, but I keep hitting permission
> errors.
>
>
> NOTE: we use the 200068 projection for all of our data, have for years.
> It's definition in SPATIAL_REF_SYS is correct.
>
>
> This works fine:
>
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
>
> But this give me an error, do I have the ST_Transform in the right spot,
> is 8.4.2 too old of a release? :
>
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text,
>
>ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)), 200068) as geom_city
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
>
> Like:
>
>
> ERROR: function st_transform(geometry) does not exist
> LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
>
> ** Error **
>
>
>
> ERROR: function st_transform(geometry) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might
> need to add explicit type casts.
> Character: 212
>
>
>
> Thanks
>
>
> bobb
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
George R. C. Silva
Desenvolvimento em GIS
http://geoprocessamento.net
http://blog.geoprocessamento.net
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
On 7 March 2012 06:10, Bob Basques wrote: > ... > ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4: ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Yeah, that was my latest fix, I don't have user rights (yet) to run against the Spatial_ref_sys table, so waiting for someone else to run it. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Also you can confirm if PROJ4 is installed by: SELECT * FROM POSTGIS_FULL_VERSION(); If PROJ4 is installed, it should appear in that string. George On Tue, Mar 6, 2012 at 3:48 PM, Mike Toews wrote: > On 7 March 2012 06:10, Bob Basques wrote: > > ... > >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > > part4::numeric),4326)), 200068) as geom_city > > ... > > ERROR: function st_transform(geometry) does not exist > > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... > > It looks like "4326)), 200068)" should be "4326), 200068))" > > -Mike > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
All, I hit this earlier in my testing, so I must have had things in the right order at some earlier point in the process. This : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326), 200068)) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; is producing this (which I did hit early on but thought I had the syntax wrong at the time) : ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or longitude exceeded limits (-14) ** Error ** ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or longitude exceeded limits (-14) SQL state: XX000 This indicates to me that the transform stuff is there , but that it failed. I haven't found many references to this error online. The numbers above are the numbers from the first record in the table. When I hit this earlier, I thought I might be trying to transform against the TEXT (those numbers) object vs the GEOM object (which I'm supposed to use, right??). The result of the ST_SetSRID seems to be the correct type of object to pass to ST_Transform based on what I found in my searches. Maybe I'm not supposed to try the transform in this manner. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Bob, can you try something like this?
select
st_astext(st_setsrid(st_geomfromtext('POINT(-48 -19)'),4326)) as "wgs84",
st_astext(st_transform(st_setsrid(st_geomfromtext('POINT(-48
-19)'),4326),29192)) as "sad69-utm22"
Here are my results:
"POINT(-48 -19)" ; "POINT(815865.331177955 7896471.32478341)"
Looking at your coordinates, I see a -93.1268 as latitude. Perhaps the
input needs a little validating?
On Tue, Mar 6, 2012 at 4:25 PM, Bob Basques wrote:
> All,
>
> I hit this earlier in my testing, so I must have had things in the right
> order at some earlier point in the process.
>
> This :
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text,
>
>ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326), 200068)) as geom_city
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
> is producing this (which I did hit early on but thought I had the syntax
> wrong at the time) :
>
> ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude
> or longitude exceeded limits (-14)
>
>
>
> ** Error **
>
>
>
> ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or
> longitude exceeded limits (-14)
> SQL state: XX000
>
> This indicates to me that the transform stuff is there , but that it
> failed. I haven't found many references to this error online. The numbers
> above are the numbers from the first record in the table. When I hit this
> earlier, I thought I might be trying to transform against the TEXT (those
> numbers) object vs the GEOM object (which I'm supposed to use, right??).
> The result of the ST_SetSRID seems to be the correct type of object to
> pass to ST_Transform based on what I found in my searches. Maybe I'm not
> supposed to try the transform in this manner.
>
> bobb
>
>
>
>
>
> >>> Mike Toews wrote:
>
> On 7 March 2012 06:10, Bob Basques wrote:
> > ...
> >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> > part4::numeric),4326)), 200068) as geom_city
> > ...
> > ERROR: function st_transform(geometry) does not exist
> > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa...
>
> It looks like "4326)), 200068)" should be "4326), 200068))"
>
> -Mike
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
George R. C. Silva
Desenvolvimento em GIS
http://geoprocessamento.net
http://blog.geoprocessamento.net
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
All, I'm no expert in postgis by any means, but I did try that, and I only got a return with the POSTGIS version. I know that all those components are there (Since I eventually got it working, but that way the POSTGIS reports things like permission error and such is very subtle IMO, and some things need to be considered in different ways. I ended up switching LAT/LON for LON/LAT in my query, which was giving me a permission denied error. Once I switched the numbers around, it all worked fine, no permission changes or anything. I've since been informed, that my erroneous query was trying to add a spatial project that didn't exist, hence the permissions error. All makes sense once it's explained, but coming into to it cold . . . another story. :c) bobb >>> George Silva wrote: Also you can confirm if PROJ4 is installed by: SELECT * FROM POSTGIS_FULL_VERSION(); If PROJ4 is installed, it should appear in that string. George On Tue, Mar 6, 2012 at 3:48 PM, Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... > ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4: ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
I'm glad it worked! On Wed, Mar 7, 2012 at 11:29 AM, Bob Basques wrote: > All, > > I'm no expert in postgis by any means, but I did try that, and I only > got a return with the POSTGIS version. I know that all those components > are there (Since I eventually got it working, but that way the POSTGIS > reports things like permission error and such is very subtle IMO, and some > things need to be considered in different ways. > > I ended up switching LAT/LON for LON/LAT in my query, which was giving > me a permission denied error. Once I switched the numbers around, it all > worked fine, no permission changes or anything. I've since been informed, > that my erroneous query was trying to add a spatial project that didn't > exist, hence the permissions error. > > All makes sense once it's explained, but coming into to it cold . . . > another story. :c) > > bobb > > > > > >>> George Silva wrote: > > Also you can confirm if PROJ4 is installed by: > > > SELECT * FROM POSTGIS_FULL_VERSION(); > > > If PROJ4 is installed, it should appear in that string. > > > George > > On Tue, Mar 6, 2012 at 3:48 PM, Mike Toews wrote: > >> On 7 March 2012 06:10, Bob Basques wrote: >> > ... >> >> > ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, >> > part4::numeric),4326)), 200068) as geom_city >> >> > ... >> >> > ERROR: function st_transform(geometry) does not exist >> > LINE 4: ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... >> >> It looks like "4326)), 200068)" should be "4326), 200068))" >> >> -Mike >> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > > > -- > George R. C. Silva > > Desenvolvimento em GIS > http://geoprocessamento.net > http://blog.geoprocessamento.net > > > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible? [SOLVED]
Dang LAT/LON LON/LAT differences. This works now, notice that the LAT/LON was switched around to LON/LAT.: -- PostGIS likes LON/LAT (NOT LAT/LON) -- DROP VIEW public.plots; CREATE VIEW plots AS select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_Transform(ST_SetSRID(ST_MakePoint(part4::numeric, part3::numeric),4326), 200068) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; bobb >>> "Bob Basques" wrote: Yeah, that was my latest fix, I don't have user rights (yet) to run against the Spatial_ref_sys table, so waiting for someone else to run it. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
