[postgis-users] Problem With Geometries and a View

[Obe, Regina]

As noted already by others it should be 
SELECT extent(the_geom) FROM geom

and your view should be 
[CODE]
CREATE VIEW geom AS
	SELECT ... GeomFromText('POINT(' || datax.value || ' ' ||
datay.value || ')') as the_geom

[CODE]
-----
an additional comment.  Is there a reason you are calculating the
geometry in the view instead of just storing it in the record table e.g.
as part of your import or with a trigger.  If you have a large table,
doing it the way your are doing it will be very slow because you will
not be able to take advantage of indexed geometries.

Thanks,
Regina


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Leonardo Mateo
Sent: Tuesday, January 30, 2007 8:48 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Problem With Geometries and a View

Hi everyone, I have a problem trying select geometries from a view. It
raises an "Invalid Geometry' error.
What I'm doing is:
- Create a view from a table with several inner joins and generating
the POINTS in the select sentence, for example:
[CODE]
   create view geom as
    select
	record.oid,
	record.recordid,
	GeomFromText('POINT(' || datax.value || ' ' || datay.value ||
')'),
	label.value
    from record
    inner join schema on schema.objectid = record.schemaid
    inner join giscontrol on giscontrol.schemaid = schema.objectid
    inner join step on step.schemaid = schema.objectid
    inner join control on control.stepid = step.objectid and
control.islabel = true
    inner join data as datax on datax.recordid = record.recordid and
datax.controlid = giscontrol.xcontrolid
    inner join data as datay on datay.recordid = record.recordid and
datay.controlid = giscontrol.ycontrolid
    inner join data as label on label.recordid = record.recordid and
label.controlid = control.objectid
    where schema.ismain = true and record.datasetid = 515
    order by recordid;
[/CODE]

 - Then check the view was created with the data I need:
[CODE]
   select * from geom;
[/CODE]
  And results like these:
46051;9105;"010100000048E17A14AE471D40713D0AD7A3704640";"ACEA
PINEROLESE INDUSTRIALE SPA"
46037;9106;"010100000048E17A14AE471D40713D0AD7A3704640";"ITALGAS"
26202;9107;"010100000048E17A14AE471D40713D0AD7A3704640";"SIPTEL"
26203;9108;"010100000048E17A14AE471D40713D0AD7A3704640";"SIPTEL"
26204;9109;"010100000048E17A14AE471D40713D0AD7A3704640";"SIPTEL"

 Which look just fine.

 - Then I try to select the extent of these points with:
 [CODE]
   select extent('the_geom') from geom;
 [/CODE]
  And I got this error:
  ERROR:  parse error - invalid geometry

The weird thing is that if I insert that SELECT sentence into another
table and create a view from that table with something like this:
  [CODE]
    create view geom as Select * from <newtable>
  [/CODE]

The extent is selected properly.

What am I doing wrong? It's driving me nuts.

Thanx a lot in advance.


-- 
Leonardo Mateo.
There's no place like ~
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.