[Xastir] anyone running Postgis?

[Tom Russo]

On Thu, Feb 04, 2010 at 08:22:38AM -0600, we recorded a bogon-computron collision of the <gerry.creager at tamu.edu> flavor, containing:
> Hmmm. I may have time between bbq'ing and napping (long story, but I'm 
> doin' about 80 lb on the smoker) I'll snag the latest, and install it, 
> then look at the script.  

I don't use postgis much, nor do I use it at all with xastir, but I got a
wile hair and wasted a lot of time last night on this.  The db_gis_postgis.sql 
script has some syntax errors.  I was able to fix a couple of them, but some 
will require more understanding of what the thing is trying to do in order 
to make it a correct script.  At this time, what's distributed with Xastir is 
broken.

The script does a "create database xastir" first, but then has comments that
you have to do multiple things outside of psql before the stuff that follows
that line.  So really, you need to create the database "xastir" and do those
things before running the script.  Then comment out "create database xastir;"
in the script before running it.

There's a syntax error in the "create table simpleStation" command.  The comma
on the line "node_path varchar(56)" is superfluous, and is rejected by at
least the version of postgres that I have (8.3.9).

The "select AddGeometryColumn" line also has an error, because it uses
"simpleStation" as second argument for the table name.  Table names are 
case insensitive in postgres, but the AddGeometryColumn of postgis uses
case-sensitive string operations to do some its work, and somehow thinks that
"simpleStation" is a table "not in the search path."  Downcasing "simpleStation"
in the second argument of AddGeometryColumn gets past this error.

Four errors remain.  There are three "alter table" lines after "-- 0 update"
that try to add columns to simpleStation that in fact are already there.

The fourth error is in the create view command, which uses "lpad" with an 
integer for the first argument.  According to the documentation, lpad takes
a string as the first argument.  In my version of Postgres, this is an error,
it reports that it cannot find a function lpad(integer, integer, unknown).

So, if I comment out the three "alter table" lines and the "create view" line,
after fixing the extra comma and AddGeometry line, the script will run in
psql.  Here are the steps:

  $ psql
  # create database xastir;
  # ^D
  $ createlang plpgsql xastir
  $ psql -f /usr/local/share/postgis/contrib/postgis.sql -d xastir
     (my system has this sql file instead of "lwpostgis.sql")
  $ psql -d xastir -f db_gis_postgis.sql

I have NO idea if xastir will work with this database once I've created it, 
since the view is not created.  

I actually have postgis installed differently than the assumption that is made
in the script, because I use postgres for nothing other than postgis (and don't
use it for that much, either).  I have done this:

  $  createlang plpgsql template1
  $ psql -f /usr/local/share/postgis/contrib/postgis.sql -d xastir
  $ psql -f /usr/local/share/postgis/contrib/spatial_ref_sys.sql -d xastir

This makes it so that all newly created databases get postgis functions.  So
creating the xastir database would be done so:

  $ createdb xastir
  $ psql -d xastir -f db_gis_postgis.sql

But the script tries to add a couple of entries to what it assumes is an
empty spatial_ref_sys table, and this gives errors (spatial_ref_sys.sql already
created them).  But that's another matter.

You still need to take care of adding the xastir_user postgis user.  I didn't
bother, and just changed the script to use an existing user for testing.

> Mike L wrote:
> > Mabe I am not running the sql script right but when I run it, its has a
> > bunch of errors. Looking at it, I see it asking to build columns for tables
> > that dont exist, etc, etc. I could even build the structure by hand If I was
> > sure of the layout.
> > 
> > Mike
> > 
> > 
> > --
> > 
> > On Wed, Feb 3, 2010 at 11:45 PM, Gerry Creager <gerry.creager at tamu.edu>wrote:
> > 
> >> Mike wrote:
> >>
> >>> Hi All,
> >>>
> >>> If anyone can help me, If you have a running Postgres/Postgis. I need a
> >>> qsl dump (or export) of your postgresql structure for the xastir database. I
> >>> can't seem to do anything with the db_gis_postgis.sql file in
> >>> ~/src/xastir/scripts/
> >>>
> >> I'm not running postgis in xastir, but I'm pretty familiar with it. What's
> >> up?
> >>
> >> 73 gerry n5jxs
> >> _______________________________________________
> >> Xastir mailing list
> >> Xastir at lists.xastir.org
> >> http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir
> >>
> > _______________________________________________
> > Xastir mailing list
> > Xastir at lists.xastir.org
> > http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir
> 
> _______________________________________________
> Xastir mailing list
> Xastir at lists.xastir.org
> http://lists.xastir.org/cgi-bin/mailman/listinfo/xastir

-- 
Tom Russo    KM5VY   SAR502   DM64ux          http://www.swcp.com/~russo/
Tijeras, NM  QRPL#1592 K2#398  SOC#236        http://kevan.org/brain.cgi?DDTNM
  In some cultures what I do would be considered normal. 
                                  -- Ineffective daily affirmation