[postgis-users] Spatial column from text via a VIEW, Possible?

[George Silva]

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',999999,'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',999999,'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 <Bob.Basques at ci.stpaul.mn.us>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" <Bob.Basques at ci.stpaul.mn.us> 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120306/594b3e0f/attachment.html>