# [postgis-users] Problem With Geometries and a View

Obe, Regina robe.dnd at cityofboston.gov
Tue Jan 30 07:43:43 PST 2007

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

[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:
PINEROLESE INDUSTRIALE SPA"

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.

--
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