[postgis-users] PostGIS and XML

[hrz at geodata.soton.ac.uk]

On Wed, Oct 12, 2005 at 04:12:28PM -0400, Kralidis,Tom [Burlington] wrote:
> Hi,
> 
> Has anyone delved into storing XML into PostGIS?  I have a requirement
> where I would like to store, and search an XML document as is (in a
> column, I guess?), and search through MapServer.

Hi Tom,

I'm sorry to say we came up against the same brick wall around a year
ago, and we're still struggling with the issue.

> The latter may be asking alot at this point, however I found the
> following on XML in PostgreSQL:
> 
> http://www.throwingbeans.org/tech/postgresql_and_xml.html

We didn't go for this approach as the software was not yet main
stream. Looks like it has come a long way since I last looked at it,
though - wow! 

> So, one could conceptually:
> 
> - setup UDFs in PostgreSQL
> - store XML documents in PostGIS tables
> - search via the article above?
> 
> Maybe there's a cleaner way to do this?

We haven't needed to search every element in an XML document, so have
extracted the data we want to search on and inserted it into the
database. Your solution should enable you to do a full search on the
data.

We have requirements now whereby we need to be able to search a
complete document, and have been looking to build on our current
structure. We're looking at storing our XML in Zebra
(http://www.indexdata.dk/zebra) and getting it to do the full search,
whilst still indexing key fields in Postgres. The downside of this is
that the amalgamation of the results from queries to zebra and
Postgres has to occur in the application (doable if you're using
mapscript). To overcome this there is, I suppose, the option of using
the zebra C API from within Postgres

Some thoughts on a thorny issue!

Kind regards,

Homme Zwaagstra