[Xastir-dev] differences between postGIS and postgres?
I'm trying to come up with a database and schema for a Qt APRs client based on xastir. It's technically not a port, although the intent is to offer it as an example xastir V2. I'm vague on the differences between standard and spatially enabled SQL databases, especially on the user interface side. If it is implemented in standard postgres will I have trouble switching to postGIS? please reply to the developer's list, or me directly. thanks, -Jason kg4wsv ___ Xastir-dev mailing list Xastir-dev@lists.xastir.org http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir-dev
Re: [Xastir-dev] differences between postGIS and postgres?
On Mon, Jan 25, 2010 at 12:13 PM, Curt, WE7U curt.w...@gmail.com wrote: As I understand it, and my understanding is pretty iffy here, the spatially-enabled databases add extra functions for doing searches based on latitude/longitude bounding boxes. That is my (incredibly limited) understanding as well. I am operating under the assumption that if I simply spec it to use postgress, then moving to postGIS would entail a db engine recompile to include GIS extensions, and possibly modifying some queries to use that extra functionality. Right now the project is specced as two parts: a data broker that will basically act as everything under the interfaces menu in xastir and shove the data into a DB, and a viewer that will display stations from the DB (on a blank background, no maps yet) encompassing the stuff in display filter and data filter menus. They are separate applications with the database as the common link. We (the software engineering instructor and myself) have tried to present xastir V2 implemented in Qt as a project, but there have been no takers. In order to make it less scary, we're presenting these two core pieces as a new project, with xastir as an example implementation. I am fairly certain we'll have some teams working on it this term. Lots is being left out as hard requirements (TNC interfacing, mapping, messaging, position reporting, all come to mind), but I'm shooting for a useful core for further development by either xastir developers or future classes. Hmm, if it doesn't use X it wouldn't be _X_astir, would it? Maybe Q-tastir? (ducks and runs) -Jason kg4wsv ___ Xastir-dev mailing list Xastir-dev@lists.xastir.org http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir-dev
Re: [Xastir-dev] differences between postGIS and postgres?
On Mon, 25 Jan 2010 10:31:09 -0600
Jason KG4WSV kg4...@gmail.com wrote:
I'm vague on the differences between standard and spatially enabled
SQL databases, especially on the user interface side. If it is
implemented in standard postgres will I have trouble switching to
postGIS?
Spatially enabled databases (native support in MySQL 4.1, or the
PostGIS add on to Postgreql), allow you to store points, lines,
polygons, and other spatial data in a form that allows you to easily
ask questions about the relationships of those data in space. For
example, with spatial data you can easily ask what are the stations
(points) that occur within this county (arbitrary polygon)? You can
use the spatial database as a data source within a GIS application,
QGIS will let you use data from a PostGIS query as a layer, OpenJump
will do the same with both MySQL and PostGIS. I've used the database
persistence layer in Xastir to store station locations in PostGIS and
then rendered a map with QGIS. Spatial databases can understand the
coordinate system that you are using for storing data, and so can
transform coordinates on the fly from one datum to another (which might
be of concern if you are working with APRS data and data that uses a
different datum (or coordinate system, like state plane feet)).
For just point data, it is possible to define a non-spatial table that
includes latitude and longitude numeric columns and to query this table
for all points within a rectangular bounding box (with the poles,
0 meridian, and possibly the 180 degree meridian potentially being
special cases if they fall inside the bounding box).
Spatial support does come with some costs. MySQL and PostGIS implement
spatial data differently, and thus need an abstraction layer if you
want your application to be database agnostic (as in the current
db_gis.c implementation in Xastir). You will likely need to work with
functions to transform data between the native storage formats in the
database and the data structures in your application. For example, the
Xastir there are internal three database specific functions to store a
station position into a simple table, upon invocation of the interface
storeStationSimpleToGisDb().
function storeStationSimplePointToGisDbPostgis()
prepares the sql query:
insert into simpleStation (station, transmit_time, position, symbol,
overlay, aprstype, origin, record_type, node_path) values ($1, $2, $3,
$4, $5, $6, $7, $8, $9)
where the value of the $3 (position) parameter is set through a call
to the xastir function xastirCoordToLatLongWKT(aStation-coord_lon,
aStation-coord_lat, wkt), which stores the position in wkt as a WGC
standard well known text representation of a point.
In contrast,
function storeStationSimplePointToGisDbMysql()
uses the same xastirCoordToLatLongWKT(aStation-coord_lon,
aStation-coord_lat, wkt) to store the position as a WGC well known
text formated point, but then invokes the MySQL function
PointFromText() to store that value in MySQL's native spatial format.
INSERT INTO simpleStationSpatial (station, transmit_time, position,
symbol, overlay, aprstype, origin, record_type, node_path) VALUES
(?,?,PointFromText(?),?,?,?,?,?,?)
And, again in contrast,
function storeStationSimplePointToDbMysql()
was intended to work with MySQL 4.0, stores the position as simple
latitude and longitude values in a non-spatial table (replacing values
in the query directly rather than using a prepared statement).
insert into simpleStation (station, symbol, overlay, aprstype,
transmit_time, latitude, longitude, origin, record_type, node_path)
values ('%s','%s','%s','%s','%s','%3.6f','%3.6f','%s','%c','%s')
For security reasons, I'd very strongly recommend using prepared queries
rather than this method, despite the added complexity in the C API for
preparing queries and binding parameters for both MySQL and Postgresql.
For a schema, I'd suggest starting with scripts/db_gis_mysql.sql or
scripts/db_gis_postgis.sql for compatibility with the existing
implementation, then I'd suggest considering implementing the APRSWorld
schema (which would allow Xastir and an APRSWorld deployment to run
over the same databse).
The comments I put in db_gis.c suggest that beyond the currently
implemented simple, schema, the roadmap might involve the addition of
storage for CAD objects (with SAR applications in mind), a full
representation of all xastir db data (stations, objects, CAD objects,
etc), and the APRSWorld schema.
I haven't done much recently with the spatial database code in Xastir,
as I've been playing with using the OpenJump API in a java application
as a test bed for what a schema for a spatial database for a SAR
application ought to look like, before trying to extend Xastir's
support for storage of CAD objects.
-Paul
--
Paul J. Morris
Biodiversity Informatics Manager
Harvard University Herbaria/Museum of Comparative Zoölogy
m...@morris.net AA3SD PGP public key available
Re: [Xastir-dev] differences between postGIS and postgres?
On 01/25/2010 11:51 AM, Jason KG4WSV wrote: On Mon, Jan 25, 2010 at 12:13 PM, Curt, WE7U curt.w...@gmail.com wrote: As I understand it, and my understanding is pretty iffy here, the spatially-enabled databases add extra functions for doing searches based on latitude/longitude bounding boxes. That is my (incredibly limited) understanding as well. I am operating under the assumption that if I simply spec it to use postgress, then moving to postGIS would entail a db engine recompile to include GIS extensions, and possibly modifying some queries to use that extra functionality. I'm still waiting for a persuasive argument for using a full-blown database for Xastir when SQLite will suffice if used properly. IMO, you guys are going _way_ overboard, here. If you want all of these features, why not simply build it around a compact version of GRASS GIS? It'll be a lot easier than trying to rewrite the world. Xaster != GIS software and personally I'd like to keep it that way. If you want GIS features, by all means, please use GIS. I'd also imagine wxWidgets would be much more portable and easier to write than Qt, but YMMV. My irrelevant $0.01. -- 73, de KB8UYR/6 Brad Douglas r...@touchofmadness.com ___ Xastir-dev mailing list Xastir-dev@lists.xastir.org http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir-dev
Re: [Xastir-dev] differences between postGIS and postgres?
Separating the gui and backend completely would go a long way toward making it more portable for sure. I've been thinking about android apps lately. One can build libraries in c but the gui must be written in java or webkit (html/js). So, gui separation may be more useful/interesting than just gtk vs qt. Android has fairly sophisticated mapping builtin as well. Apps can present layers to google maps and navigation, for example. On Jan 25, 2010 5:43 PM, Curt, WE7U curt.w...@gmail.com wrote: On Mon, 25 Jan 2010, Brad Douglas wrote: I'm still waiting for a persuasive argument for using a ... I'd like to see SQLite as one of the options as well, for less-intensive stand-alone setups and for PDA's, etc. Having rewritten the in-memory code a few times to make it faster with large amounts of data, I can appreciate having a separate database to hold this info. It also gets us a long way towards both the persistence goals and the distributed app goals. The in-memory database strategy has been stretched a bit too far as it is. It's a quad-linked list, with a separate hash table to speed up searching. A convoluted mess. I'd also imagine wxWidgets would be much more portable and easier to write than Qt, but YMMV I looked at WxWidgets a while back, when Qt wasn't free for Windows and so wasn't an option for us. There were some things about WxWidgets that turned me off back then, but I'd have to see if I wrote down notes as to the reasons. I may not have. Now that Qt is free for use with GPL'ed applications it is by far the more attractive option. There's no reason that the back-end couldn't be written w/o GUI code, then GUI applications written with different widget sets to talk to it. People that didn't like one widget set could replace it with another. If we're careful in the design this replacement could be made easier to do. -- Curt, WE7U. http://www.eskimo.com/~archer APRS: Where it's at! ... Xastir-dev mailing list Xastir-dev@lists.xastir.org http://lists.xastir.org/cgi-bin/mailman/listinfo... ___ Xastir-dev mailing list Xastir-dev@lists.xastir.org http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir-dev
