Re: GIS Tools/modeling

[cjhowe]

There is a lot of crazy stuff going on to make/utilize a spatial database.  Before I go digging around for an answer to this, maybe someone can critique this approach and how it works against the OFBiz entity engine.

In order to access anything useful from a geospatial database (in my case PostgreSQL using PostGis) you have to be able to call functions.  So, first question:  Are there tools that exist already in OFBiz to call functions that exist in the database or do you _have to use the SQLProcessor to run a query with a function?

Second question: The data is stored as a "geometry" data type in the database.  Most everything that is going to be consumed by OFBiz (Google Maps, etc) AFAICT, wants coordinate pairs and not geometry object.   Is it better performance/development wise to create those functions in the database or to process the data in OFBiz?

For a little background, this is the kind of query necessary to get a coordinate pair from a polygon geometry:

select gid, (
((select st_x(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),1),4030))) from precinct where gid=1),
(select st_y(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),1),4030))) from precinct where gid=1)),
((select st_x(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),2),4030))) from precinct where gid=1),
(select st_y(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),2),4030))) from precinct where gid=1)),
((select st_x(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),3),4030))) from precinct where gid=1),
(select st_y(astext(transform(st_pointn(st_exteriorring(ST_GeometryN(the_geom, 1)),3),4030))) from precinct where gid=1))
...n->number of points in polygon
)
from precinct where gid =1;

----- Original Message ----
From: Al Byers <[hidden email]>
To: [hidden email]
Sent: Friday, December 7, 2007 12:47:37 AM
Subject: Re: GIS Tools/modeling


Chris,

Sorry, I did not get around to implementing os geocoders. Here are some
links I came across, however:



http://www.ontok.com/geocode/compare
http://www.ontok.com/api


http://geocoder.us/

One possible alternative is to find the code for a geocoder and use
 some of
the free data that is available for it to operate on. I did not look
 into
that route, but it seems there should be plenty of examples out there
 and
tons of TIGER  data.

-Al