[SQL] a spatial table's bounding box

[Asli Akarsakarya]

Hello,  My question is a basic one for postGIS, I believe. It should have a 
primitive answer somewhere, but I don't have it.  I want to get the extensions 
of  spatial table. Say that there is a spatial table named 'roads', and a some 
road geometry inside. I want to fetch the bounding box (envelope or extension) 
for that table, that means for all the roads that it has.  According the OGC 
specification, this should be defined in the "geometry_columns" table. As four 
columns, "minX", "minY", "maxX" and "maxY". But I couldn't find it anywhere on 
my postgis. Yet. From which table/view? With what SQL? Thanks for your answers, 
Aslı.

[SQL] help with xpath namespace

[Brian Sherwood]

I am trying to use xpath to extract some information from a XML document.
(if it matters, It's the router config for a Juniper router)

I believe I am having problems with the namespace.
I am trying to extract the serial-number, but I am not getting anything.

Here is the script I have been testing with:


BEGIN;

CREATE TABLE "xml_test" (
data_xmlxml
);


INSERT INTO xml_test (data_xml) VALUES ('
 http://xml.juniper.net/junos/9.6R4/junos-chassis";>
   
 Chassis
 JN11
 MX960
   
 ');


select data_xml from xml_test where data_xml is document;

select (xpath('/chassis-inventory/chassis/serial-number/text()',
data_xml,
ARRAY[ARRAY['junos',
'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
   )) from xml_test;

ROLLBACK;


This gives me the following:


BEGIN
CREATE TABLE
INSERT 0 1
   data_xml
---
  http://xml.juniper.net/junos/9.6R4/junos-chassis";>+
 +
  Chassis+
  JN11 +
  MX960+
+
  
(1 row)

 xpath
---
 {}
(1 row)

ROLLBACK


Can anyone suggest how I would go about getting the serial-number with xpath?

Thanks

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help with xpath namespace

[Filip Rembiałkowski]

2011/9/22 Brian Sherwood 

>
> select (xpath('/chassis-inventory/chassis/serial-number/text()',
>data_xml,
>ARRAY[ARRAY['junos',
> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
>   )) from xml_test;
>
> Can anyone suggest how I would go about getting the serial-number with
> xpath?
>
>

http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING-
see "mydefns".

This will work:

select xpath(
'/junos:chassis-inventory/junos:chassis/junos:serial-number/text()',
data_xml,
ARRAY[ARRAY['junos', 'http://xml.juniper.net/junos/9.6R4/junos-chassis'
]]
)
from xml_test;



cheers, Filip

Re: [SQL] a spatial table's bounding box

[Filip Rembiałkowski]

2011/9/22 Asli Akarsakarya 

>
> I want to get the extensions of  spatial table. Say that there is a spatial 
> table named 'roads', and a some road geometry inside. I want to fetch the 
> bounding box (envelope or extension) for that table, that means for all the 
> roads that it has.
>
> According the OGC specification, this should be defined in the 
> "geometry_columns" table. As four columns, "minX", "minY", "maxX" and "maxY". 
> But I couldn't find it anywhere on my postgis. Yet.
>
> From which table/view? With what SQL?
>
>
>
I would try

SELECT st_envelope( st_collect(geom) ) FROM roads;


HTH, Filip

[SQL] Howto build a funtion that selects an id or inserts a value

[Andreas]

Hi,

I'd like to have a function that looks up an id of an item.
In case the item doesn't exist in the table yet it should be inserted 
and the new id should be returned.


From the PG docu, I took the merge_db sample and modified it a bit.
This works but I'm wondering if INSERT part could be tuned.
Could I have something like
i :=  INSERT INTO _log.computer ( item ) VALUES ( data ) returning id;
so I dont have to query the sequence and create another roundtrip on the 
network?



CREATE or replace FUNCTION find_or_insert_item ( data TEXT ) RETURNS 
integer AS

$$
declare
i   integer;
BEGIN
LOOP
-- first try to select the id of an item
select id into i from items where item ilike data;
IF found THEN
RETURN i;
END IF;
-- not there, so try to insert the item and retrieve the new id.
-- if someone else inserts the same item concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO items ( item ) VALUES ( data );
select currval('items_id_seq') into i;
RETURN i;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the SELECT again.
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql