Re: [postgis-users] upgrading PostGIS and Postgres

[John Callahan]

Gotcha.  Sorry about that.  I confused the binaries with the installers.

- John





On Fri, Jul 20, 2012 at 4:02 PM, Bborie Park  wrote:

>
> http://postgis.refractions.net/download/windows/pg90/postgis-pg90-binaries-2.0.1w32.zip
>
>
> http://postgis.refractions.net/download/windows/pg90/postgis-pg90-binaries-2.0.1w64.zip
>
> Those binary archives are from the page.  If you're looking for an NSIS
> installer, it doesn't look like one is available.
>
> -bborie
>
> On 07/20/2012 12:51 PM, John Callahan wrote:
> > Yes, but I do not see 2.0.1 binaries for PG 9.0.
> >
> > - John
> >
> >
> >
> >
> >
> > On Fri, Jul 20, 2012 at 3:03 PM, Bborie Park  wrote:
> >
> >> PostGIS 2.0 supports any PostgreSQL version of or after 8.4.
> >>
> >> By the looks of the page you link to, there are binaries for 9.0.
> >>
> >> -bborie
> >>
> >> On 07/20/2012 11:55 AM, John Callahan wrote:
> >>> Thanks for the response.  I will follow your suggestion.
> >>>
> >>> However, is PostGIS 2.0.1 supported on Postgres 9.0?   I had thought it
> >> was
> >>> but do not see a Windows installer for that combination on
> >>> http://postgis.refractions.net/download/windows/
> >>>
> >>> - John
> >>>
> >>> ***
> >>> John Callahan, Research Scientist
> >>> Delaware Geological Survey, University of Delaware
> >>> URL: http://www.dgs.udel.edu
> >>> *
> >>>
> >>>
> >>>
> >>> On Fri, Jul 20, 2012 at 12:46 PM, Bborie Park 
> >> wrote:
> >>>
> >>>> Also, backup everything first with pg_dump.
> >>>>
> >>>> On 07/19/2012 09:08 PM, John Callahan wrote:
> >>>>> I apologize for what may be a trivial question.  I want to upgrade
> both
> >>>>> Postgres and PostGIS but not sure which to upgrade first.  I am
> >> currently
> >>>>> running Postgres 9.0.7 and PostGIS 2.0 beta (from Jan 2012.)  I would
> >>>> like
> >>>>> to go to Postgres 9.1 and PostGIS 2.0.1.  (I'm working in Windows.)
> >>>>>
> >>>>> Do I upgrade Postgres or PostGIS first?  Does it matter?   Thanks.
> >>>>>
> >>>>> - John
> >>>>>
> >>>>> ***
> >>>>> John Callahan, Research Scientist
> >>>>> Delaware Geological Survey, University of Delaware
> >>>>> URL: http://www.dgs.udel.edu
> >>>>> *
> >>>>>
> >>>>>
> >>>>>
> >>>>> ___
> >>>>> postgis-users mailing list
> >>>>> postgis-users@postgis.refractions.net
> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>>>
> >>>>
> >>>> --
> >>>> Bborie Park
> >>>> Programmer
> >>>> Center for Vectorborne Diseases
> >>>> UC Davis
> >>>> 530-752-8380
> >>>> bkp...@ucdavis.edu
> >>>>
> >>>>
> >>>> ___
> >>>> postgis-users mailing list
> >>>> postgis-users@postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>>
> >>>
> >>>
> >>>
> >>> ___
> >>> postgis-users mailing list
> >>> postgis-users@postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>
> >> --
> >> Bborie Park
> >> Programmer
> >> Center for Vectorborne Diseases
> >> UC Davis
> >> 530-752-8380
> >> bkp...@ucdavis.edu
> >>
> >>
> >> ___
> >> postgis-users mailing list
> >> postgis-users@postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] upgrading PostGIS and Postgres

[John Callahan]

Yes, but I do not see 2.0.1 binaries for PG 9.0.

- John





On Fri, Jul 20, 2012 at 3:03 PM, Bborie Park  wrote:

> PostGIS 2.0 supports any PostgreSQL version of or after 8.4.
>
> By the looks of the page you link to, there are binaries for 9.0.
>
> -bborie
>
> On 07/20/2012 11:55 AM, John Callahan wrote:
> > Thanks for the response.  I will follow your suggestion.
> >
> > However, is PostGIS 2.0.1 supported on Postgres 9.0?   I had thought it
> was
> > but do not see a Windows installer for that combination on
> > http://postgis.refractions.net/download/windows/
> >
> > - John
> >
> > ***
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > *
> >
> >
> >
> > On Fri, Jul 20, 2012 at 12:46 PM, Bborie Park 
> wrote:
> >
> >> Also, backup everything first with pg_dump.
> >>
> >> On 07/19/2012 09:08 PM, John Callahan wrote:
> >>> I apologize for what may be a trivial question.  I want to upgrade both
> >>> Postgres and PostGIS but not sure which to upgrade first.  I am
> currently
> >>> running Postgres 9.0.7 and PostGIS 2.0 beta (from Jan 2012.)  I would
> >> like
> >>> to go to Postgres 9.1 and PostGIS 2.0.1.  (I'm working in Windows.)
> >>>
> >>> Do I upgrade Postgres or PostGIS first?  Does it matter?   Thanks.
> >>>
> >>> - John
> >>>
> >>> ***
> >>> John Callahan, Research Scientist
> >>> Delaware Geological Survey, University of Delaware
> >>> URL: http://www.dgs.udel.edu
> >>> *
> >>>
> >>>
> >>>
> >>> ___
> >>> postgis-users mailing list
> >>> postgis-users@postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>
> >> --
> >> Bborie Park
> >> Programmer
> >> Center for Vectorborne Diseases
> >> UC Davis
> >> 530-752-8380
> >> bkp...@ucdavis.edu
> >>
> >>
> >> ___
> >> postgis-users mailing list
> >> postgis-users@postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] upgrading PostGIS and Postgres

[John Callahan]

Thanks for the response.  I will follow your suggestion.

However, is PostGIS 2.0.1 supported on Postgres 9.0?   I had thought it was
but do not see a Windows installer for that combination on
http://postgis.refractions.net/download/windows/

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Fri, Jul 20, 2012 at 12:46 PM, Bborie Park  wrote:

> Also, backup everything first with pg_dump.
>
> On 07/19/2012 09:08 PM, John Callahan wrote:
> > I apologize for what may be a trivial question.  I want to upgrade both
> > Postgres and PostGIS but not sure which to upgrade first.  I am currently
> > running Postgres 9.0.7 and PostGIS 2.0 beta (from Jan 2012.)  I would
> like
> > to go to Postgres 9.1 and PostGIS 2.0.1.  (I'm working in Windows.)
> >
> > Do I upgrade Postgres or PostGIS first?  Does it matter?   Thanks.
> >
> > - John
> >
> > ***
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > *
> >
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] upgrading PostGIS and Postgres

[John Callahan]

I apologize for what may be a trivial question.  I want to upgrade both
Postgres and PostGIS but not sure which to upgrade first.  I am currently
running Postgres 9.0.7 and PostGIS 2.0 beta (from Jan 2012.)  I would like
to go to Postgres 9.1 and PostGIS 2.0.1.  (I'm working in Windows.)

Do I upgrade Postgres or PostGIS first?  Does it matter?   Thanks.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Installation problem with postgis-pg91-binaries-2.0.0beta4w32

[John Callahan]

I had that problem once.  It was related to a missing (or outdated)
libxml2-2.dll in the /bin folder.  I would stop the postgres service and
try manually recopying all of the files to their appropriate locations.

- John






On Thu, Mar 29, 2012 at 6:25 AM,  wrote:

> Dear list,
> we are trying to install the latest available experimental binaries for
> windows
> (postgis-pg91-binaries-2.0.0beta4w32).
> We installed postgres 9.1 (32bit) and than run the installer file
> 'makepostgisdb_using_extensions.bat' with the proper (hopefully) set.
> We get this error:
>
> "C:\Program Files (x86)\PostgreSQL\9.1\bin\psql"  -d "sam" -c "CREATE
> EXTENSION
> postgis;"
> ERROR:  could not load library "C:/Program Files
> (x86)/PostgreSQL/9.1/lib/postgis-2.0.dll": The specified module could not
> be found.
>
> We are installing on a Windows Server 2008 R2 64-bit and we have installed
> postgresql-9.1.3-1-windows.exe (32-bit)
>
> The file 'makepostgisdb_using_extensions.bat' look like this:
> REM this is an example of how to create a new db and spatially enable it
> using
> CREATE EXTENSION
> set PGPORT=5432
> set PGHOST=localhost
> set PGUSER=postgres
> set PGPASSWORD=***
> set THEDB=sam
> REM PGINSTALL=C:\Program Files\PostgreSQL\9.1
> set PGINSTALL=C:\Program Files (x86)\PostgreSQL\9.1
> set PGADMIN=%PGINSTALL%\pgAdmin III
> set PGBIN=%PGINSTALL%\bin\
> set PGLIB=%PGINSTALL%\lib\
> set POSTGISVER=2.0
> xcopy bin\*.* "%PGBIN%"
> xcopy /I /S bin\postgisgui\* "%PGBIN%\postgisgui"
> xcopy /I plugins.d "%PGADMIN%\plugins.d"
> xcopy lib\*.* "%PGLIB%"
> xcopy share\extension\*.* "%PGINSTALL%\share\extension"
> xcopy /I share\contrib\postgis-%POSTGISVER%
> "%PGINSTALL%\share\contrib\postgis-%POSTGISVER%"
> "%PGBIN%\psql"  -c "CREATE DATABASE %THEDB%"
> "%PGBIN%\psql"  -d "%THEDB%" -c "CREATE EXTENSION postgis;"
> "%PGBIN%\psql"  -d "%THEDB%" -c "CREATE EXTENSION postgis_topology;"
>
> REM Uncomment the below line if this is a template database
> REM "%PGBIN%\psql" -d "%THEDB%" -c "UPDATE pg_database SET datistemplate =
> true
> WHERE datname = '%THEDB%';GRANT ALL ON geometry_columns TO PUBLIC; GRANT
> ALL ON
> spatial_ref_sys TO PUBLIC"
>
>
> "C:/Program Files (x86)/PostgreSQL/9.1/lib/postgis-2.0.dll" is where it is
> supposed to be.
> Any suggestion?
>
> Thanks a lot
>
> Ferdinando
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS Raster and IDW

[John Callahan]

The gdal_grid tool supports IDW interpolation.  I've used ths for CSV files
to TIFs.  Very recent versions of GDAL should be able to read/write to
postgis rasters.   Check out  http://www.gdal.org/gdal_grid.html

Personally, I would love to have postgis do spatial interpolations.
 However, the last I checked into this, PostGIS does not really have any
spatial interpolation methods (IDW, kriging, splines) built in.  I saw some
examples of people integrating R functions into postgres pl/sql:
http://www.joeconway.com/plr/

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Tue, Mar 27, 2012 at 11:20 PM, Blair Deaver wrote:

> Hello,
>
> I am trying to find out if PostGIS Raster supports the Inverse Distance
> Weighted interpolation method via some function.  I am specifically trying
> to generate a heat map from a geometry table with point locations.  Can
> some one point me in the right direction?  Thanks.
>
> Cheers,
> Blair
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] problem with restoring database postgis 2.0

[John Callahan]

Thanks Steve.  Great idea.  Unfortunately, I don't think it helps me now
(unless I want to reload 70 or so datasets.)   I'll keep it in mind for
other databases.

I'm still learning postgis but how do you keep the functions in the data
schema if the database is created (with functions) before that schema?  Or
should all the functions stay in the public schema and datasets in the
"data" schema?  Thanks for any help.

- John





On Thu, Mar 22, 2012 at 8:11 PM, Stephen Woodbridge  wrote:

> This is why I ALWAYS build my databases and create a data schema than set
> the search path to "data, public". Then all my functions and data go into
> "data" and I can pg_dump -N public to leave behind all the postgis stuff.
>
> createdb -T template_postgis mydb
> psql mydb -c "create schema data; alter database mydb set search_path to
> data, public"
> ...
> pg_dump ... -f mydb.dump -N public mydb
>
> Never have to worry about that again.
>
> -Steve
>
>
> On 3/22/2012 8:02 PM, John Callahan wrote:
>
>> Well, that makes sense.  I was using the --data-only option because I
>> did not want to bring over the functions (since the functions already
>> exist in the new database through the postgis creation script.)
>>
>> Is there a way to dump/restore only the data tables (including create
>> statements, indexes,  sequences, privileges) and not the functions or
>> function comments?  I ran into a problem recently while upgrading when I
>> had too many similar functions and postgis couldn't determine a unique
>> function at times.
>>
>> - John
>>
>> *
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> ***
>>
>>
>>
>> On Thu, Mar 22, 2012 at 7:17 PM, Bborie Park > <mailto:bkp...@ucdavis.edu>> wrote:
>>
>>    The pg_dump call is called with "--data-only" so you're not getting
>>any table creation information.  And since the new DB doesn't have
>>any tables, restoring the data will result in an error.
>>
>>-bborie
>>
>>
>>On 03/22/2012 04:14 PM, John Callahan wrote:
>>
>>I having a problem with something that is usually straight
>>forward. I am
>>moving a database from a Windows 2003 Server box to Windows 2008
>>Server.
>>  Both are x64 but running 32bit Postgres 9.0.7.  The
>>new/destination
>>database runs the latest postgis 2.0 binaries (from March 19 or
>>so) and the
>>source/older database runs postgis 2.0 libraries from a few
>>months ago.
>>
>>On the new db, I installed postgres 9.0.7, then postgis 2.0
>>latest build,
>>which created my database.  Of course, the database does not
>>have any
>>tables at this point (just the functions, a few views.)
>>
>>I run pg_dump on the source db as so:
>>pg_dump.exe --host localhost --port 5432 --username "username"
>>--role
>>"myrole"  --format plain --data-only --verbose --file
>>"C:\temp\webdata_20120315.sql" "mydb"
>>
>>    When I try to run the resulting sql on the new server, I get the
>>following
>>error:
>>ERROR: relation  does not exist
>>
>>If I use --inserts options in pg_dump, I still get the "relation
>>does not
>>exist error"   If I use pgAdmin to backup and restore (using the
>> tar
>>format), the error is the same.
>>
>>What am I missing?   Thanks.
>>
>>- John
>>
>>** *
>>John Callahan, Research Scientist
>>Delaware Geological Survey, University of Delaware
>>URL: http://www.dgs.udel.edu
>>** ***
>>
>>
>>
>>
>>__ _
>>postgis-users mailing list
>>postgis-users@postgis. refractions.net
>>
>> <mailto:postgis-users@postgis.**refractions.net
>> >
>>http://postgis.refractions. net/mailman/listinfo/postgis- users
>>
>>
>> <http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailm

Re: [postgis-users] problem with restoring database postgis 2.0

[John Callahan]

Well, that makes sense.  I was using the --data-only option because I did
not want to bring over the functions (since the functions already exist in
the new database through the postgis creation script.)

Is there a way to dump/restore only the data tables (including create
statements, indexes,  sequences, privileges) and not the functions or
function comments?  I ran into a problem recently while upgrading when I
had too many similar functions and postgis couldn't determine a unique
function at times.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Thu, Mar 22, 2012 at 7:17 PM, Bborie Park  wrote:

> The pg_dump call is called with "--data-only" so you're not getting any
> table creation information.  And since the new DB doesn't have any tables,
> restoring the data will result in an error.
>
> -bborie
>
>
> On 03/22/2012 04:14 PM, John Callahan wrote:
>
>> I having a problem with something that is usually straight forward. I am
>> moving a database from a Windows 2003 Server box to Windows 2008 Server.
>>  Both are x64 but running 32bit Postgres 9.0.7.  The new/destination
>> database runs the latest postgis 2.0 binaries (from March 19 or so) and
>> the
>> source/older database runs postgis 2.0 libraries from a few months ago.
>>
>> On the new db, I installed postgres 9.0.7, then postgis 2.0 latest build,
>> which created my database.  Of course, the database does not have any
>> tables at this point (just the functions, a few views.)
>>
>> I run pg_dump on the source db as so:
>> pg_dump.exe --host localhost --port 5432 --username "username" --role
>> "myrole"  --format plain --data-only --verbose --file
>> "C:\temp\webdata_20120315.sql" "mydb"
>>
>> When I try to run the resulting sql on the new server, I get the following
>> error:
>> ERROR: relation  does not exist
>>
>> If I use --inserts options in pg_dump, I still get the "relation does not
>> exist error"   If I use pgAdmin to backup and restore (using the tar
>> format), the error is the same.
>>
>> What am I missing?   Thanks.
>>
>> - John
>>
>> *
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> ***
>>
>>
>>
>>
>> __**_
>> postgis-users mailing list
>> postgis-users@postgis.**refractions.net
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> __**_
> postgis-users mailing list
> postgis-users@postgis.**refractions.net
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] problem with restoring database postgis 2.0

[John Callahan]

I having a problem with something that is usually straight forward. I am
moving a database from a Windows 2003 Server box to Windows 2008 Server.
 Both are x64 but running 32bit Postgres 9.0.7.  The new/destination
database runs the latest postgis 2.0 binaries (from March 19 or so) and the
source/older database runs postgis 2.0 libraries from a few months ago.

On the new db, I installed postgres 9.0.7, then postgis 2.0 latest build,
which created my database.  Of course, the database does not have any
tables at this point (just the functions, a few views.)

I run pg_dump on the source db as so:
pg_dump.exe --host localhost --port 5432 --username "username" --role
"myrole"  --format plain --data-only --verbose --file
"C:\temp\webdata_20120315.sql" "mydb"

When I try to run the resulting sql on the new server, I get the following
error:
ERROR: relation  does not exist

If I use --inserts options in pg_dump, I still get the "relation does not
exist error"   If I use pgAdmin to backup and restore (using the tar
format), the error is the same.

What am I missing?   Thanks.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Windows Binaries for PostGis 2.0

[John Callahan]

I've been using PostGIS 2.0 on Windows for a while.

http://postgis.refractions.net/download/windows/experimental.php

- John





On Wed, Mar 21, 2012 at 4:53 AM, Giannis Giakoumidakis <
ggiakoumida...@yahoo.com> wrote:

> I know that I'm a bit hurry but I wanted to ask when approximately we will
> be able to use the latest PostGis version (2.0) in Windows? One month or
> much later?
>
> Thanx.
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] extra function?

[John Callahan]

Steve,

I had a similar issue.  If you look at your list of functions (say through
pgAdmin), you'll probably see multiple instances of that function.  If the
same function is listed more than once, with the same number and type of
arguments, then it will (I think?) report as not unique.  In that case, I
manually deleted the duplicate functions, and then added them back.  To add
them back, look inside the rt_postgis.sql (or postgis.sql) file than comes
with the distribution (I'm using alpha5).  you'll see all of the functions
in there.  Just cut and paste the necessary CREATE OR REPLACE statements
for your functions into the Query Tool or psql console window.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Wed, Feb 22, 2012 at 4:28 PM, Bborie Park  wrote:

> Steve,
>
> I know enough to do my own testing and make the necessary changes. Until I
> have the time to do so, the following should work.
>
> CREATE OR REPLACE FUNCTION st_summarystats(rast raster,
> exclude_nodata_value boolean)
> RETURNS summarystats
> AS $$ SELECT _st_summarystats($1, 1, $2, 1.) $$
> LANGUAGE 'SQL' IMMUTABLE STRICT;
>
> -bborie
>
> On 02/22/2012 12:16 PM, Stephen Crawford wrote:
>
>> Tried running it and get this:
>>
>> "ERROR: function _st_summarystats(raster, integer, boolean, integer) is
>> not unique
>> LINE 3: AS $$ SELECT _st_summarystats($1, 1::int, $2, 1) $$
>> ^
>> HINT: Could not choose best candidate function. You might need to add
>> explicit type casts"
>>
>> -Steve
>>
>> On 2/22/2012 1:59 PM, Bborie Park wrote:
>>
>>> Interesting. Can you run the following and try again?
>>>
>>> CREATE OR REPLACE FUNCTION st_summarystats(rast raster,
>>> exclude_nodata_value boolean)
>>> RETURNS summarystats
>>> AS $$ SELECT _st_summarystats($1, 1::int, $2, 1) $$
>>> LANGUAGE 'SQL' IMMUTABLE STRICT;
>>>
>>> If that removes the HINT, I'll make the appropriate changes to the code.
>>>
>>> -bborie
>>>
>>> On 02/22/2012 10:48 AM, Stephen Crawford wrote:
>>>
>>>> I only tried the casting because of the messages I get, on of which is:
>>>>
>>>> "function _st_summarystats($1, 1, $2, 1) is not unique"
>>>> and
>>>> "HINT: Could not choose best candidate function. You might need to add
>>>> explicit type casts"
>>>>
>>>> Thanks,
>>>> steve
>>>>
>>>>
>>>>
>>>> On 2/22/2012 1:41 PM, Bborie Park wrote:
>>>>
>>>>> Hey Steve,
>>>>>
>>>>> _st_summarystats is an underlying function that isn't meant for use by
>>>>> end-users. ST_SummaryStats is what you should be using.
>>>>>
>>>>> The example query you provided shouldn't have any issues and shouldn't
>>>>> be creating any of those messages. In your situation, you don't even
>>>>> need the true::boolean.
>>>>>
>>>>> -bborie
>>>>>
>>>>> On 02/22/2012 10:34 AM, Stephen Crawford wrote:
>>>>>
>>>>>> Should I have both _st_summarystats and st_summarystats in my
>>>>>> database?
>>>>>> I get messages like "function not unique" and "could not choose best
>>>>>> candidate function" when doing this:
>>>>>>
>>>>>> SELECT rid, observation_date, ST_SummaryStats(rast::raster,
>>>>>> true::boolean) FROM spring_ms WHERE observation_date = '2012-02-20';
>>>>>>
>>>>>> I expected to get 187 records (one for each tile).
>>>>>>
>>>>>> Thanks,
>>>>>> Steve
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> __**_
> postgis-users mailing list
> postgis-users@postgis.**refractions.net
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis raster st_value

[John Callahan]

Thanks for the advice.  I did retile my rasters down to 100x100 simply be
dropping and reloading (easy enough through a simple script.)   I didn't
notice an appreciable difference although I didn't check the logs for exact
times.

Interesting thought on putting all tiles for all layers in the same table.
 I had to read that a few times to understand it.  I will try that and
compare to what I have now, which is a php script that queries each of my
17 raster layers and returns those results in a couple of seconds.  Good
enough for now.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Tue, Feb 21, 2012 at 10:32 AM, Pierre Racine  wrote:

> > Thank you.  It works great!  The following works for a single point
> against a
> > single raster:
> >
> > SELECT
> > ST_Value(rast, ST_SetSRID(ST_Point(187251.0, 119422.0), 26957)) FROM
> geol1
> > WHERE ST_Intersects(rast, ST_SetSRID(ST_Point(187251.0, 119422.0),
> > 26957)::geometry, 1);
>
> It will work even faster if you retile everything to smaller tiles and
> create an index on your raster column (which I have no doubt you did). You
> can use the new ST_Tile() plpgsql prototype to retile everything without
> reloading:
>
>
> http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_tile.sql
>
> It's a bit slow but can work overnight...
>
> > Could anyone recommend the best way to query multiple rasters using the
> same
> > point for each?  I've seen examples pulling multiple points from the
> same rasters
> > but not the same point from multiple rasters.  I have about 10-20
> rasters (geol2,
> > geol3..) I need to find the value of at a single point.
>
> If you have an application that want to do that often I would recommend to
> store all the tiles for all the layers in the same table with a column
> identifying the semantic of each set of tile. You then just do a query like
> you did, adding the point id and the semantic column. For each point you
> will get as many rows as you have layers. Transforming everything in a one
> multiple column row is another story: You have to JOIN the table with
> itself as many time as you have layers.
>
> Pierre
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis raster st_value

[John Callahan]

Thank you.  It works great!  The following works for a single point against
a single raster:

SELECT
ST_Value(rast, ST_SetSRID(ST_Point(187251.0, 119422.0), 26957))
FROM geol1
WHERE ST_Intersects(rast, ST_SetSRID(ST_Point(187251.0, 119422.0),
26957)::geometry, 1);


Could anyone recommend the best way to query multiple rasters using the
same point for each?  I've seen examples pulling multiple points from the
same rasters but not the same point from multiple rasters.  I have about
10-20 rasters (geol2, geol3..) I need to find the value of at a single
point.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Mon, Feb 20, 2012 at 4:29 PM, Pierre Racine
wrote:

> Use ST_Intersects(raster, geometry) in the WHERE clause (as you would do
> with geometries).
>
> Pierre
>
> > -Original Message-
> > From: postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-
> > boun...@postgis.refractions.net] On Behalf Of John Callahan
> > Sent: Monday, February 20, 2012 4:28 PM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] postgis raster st_value
> >
> > Hopefully this is a question with a quick answer
> >
> > I have a few rasters loaded into PostGIS 2.0 alpha5.  I loaded them with
> tiles at
> > 256x256 px (no real reason why I chose those dimensions.)  When I run a
> simple
> > ST_Value() statement, returned is one record for each tile.  And, of
> course, every
> > one of those will be blank except for the tile that contains the
> location.  Is there
> > a way to return only the value, and not a full array of nearly empty
> cells?   (of
> > course, only one value is returned when the raster is not-tiled but I'm
> not sure if
> > that's the way to go.)  Thanks.
> >
> >
> > - John
> >
> > ***
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > *
> >
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] postgis raster st_value

[John Callahan]

Hopefully this is a question with a quick answer

I have a few rasters loaded into PostGIS 2.0 alpha5.  I loaded them with
tiles at 256x256 px (no real reason why I chose those dimensions.)  When I
run a simple ST_Value() statement, returned is one record for each tile.
 And, of course, every one of those will be blank except for the tile that
contains the location.  Is there a way to return only the value, and not a
full array of nearly empty cells?   (of course, only one value is returned
when the raster is not-tiled but I'm not sure if that's the way to go.)
 Thanks.


- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis upgrade functions question

[John Callahan]

Thanks for your response.  I missed those instructions.  I was following
the README file that came with the alpha5 version.  Didn't think to check
the website.  Thanks!

My guess is that the old version of PostGIS I had installed was before the
alpha releases.  The version name reported was simply "PostGIS 2.0.0SVN".

I am sure the "function not unique" error has something to do with my
loading the old functions back in there via the database import.  I did run
uninstall_legacy.sql and reduced the number of functions to 957.  (I didn't
see a uninstall_legacy_raster.sql though, which is probably where the
problem lies.) I may just manually remove then reinstall all of the
functions.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Mon, Feb 20, 2012 at 2:12 PM, Bborie Park  wrote:

> John,
>
> Based upon what you're describing, it sounds like you do have
> duplicative sets of functions.  As I don't know what version of
> PostGIS you upgraded from, I can't provide any specific advice.  At
> the moment, the hard upgrade needs to be following the instructions
> described at:
>
>
> http://postgis.refractions.net/documentation/manual-svn/postgis_installation.html#hard_upgrade
>
> -bborie
>
> On Mon, Feb 20, 2012 at 11:05 AM, John Callahan 
> wrote:
> > I recently upgraded to PostGIS alpha5 version (using Postgres 9.0.2) on
> > Windows.   I believe I performed a hard upgrade (dumped my old database,
> ran
> > postgis install scripts into a new database, import previous database
> dump.)
> >   All seemed to work well.
> >
> > Now, I am working with raster data.  I loaded a test raster fine.  When I
> > tried to access that data, I received "ERROR:  function st_value(raster,
> > geometry) is not unique".   When I checked through pgAdmin, I see about
> 1026
> > functions in my database.  ST_Value is in there 8 times with different
> > variations, some with the same number of arguments.
> >
> > Could this be because when I exported my old database, I used pg_dumpall,
> > which included the functions, on top of what was already there?  As a
> fix,
> > could I simply delete all of the functions through pgAdmin, then
> > run postgis-pg90-binaries-2.0.0alpha5\share\contrib\postgis-2.0\
> postgis.sql
> > and rtpostgis.sql?
> >
> > - John
> >
> > ***
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > *
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] postgis upgrade functions question

[John Callahan]

I recently upgraded to PostGIS alpha5 version (using Postgres 9.0.2) on
Windows.   I believe I performed a hard upgrade (dumped my old database,
ran postgis install scripts into a new database, import previous database
dump.)   All seemed to work well.

Now, I am working with raster data.  I loaded a test raster fine.  When I
tried to access that data, I received "ERROR:  function st_value(raster,
geometry) is not unique".   When I checked through pgAdmin, I see about
1026 functions in my database.  ST_Value is in there 8 times with different
variations, some with the same number of arguments.

Could this be because when I exported my old database, I used pg_dumpall,
which included the functions, on top of what was already there?  As a fix,
could I simply delete all of the functions through pgAdmin, then
run postgis-pg90-binaries-2.0.0alpha5\share\contrib\postgis-2.0\
postgis.sql and rtpostgis.sql?

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] migration/upgrade advice

[John Callahan]

Thanks Regina.  The version I'm running was downloaded last August from
http://www.postgis.org/download/windows/experimental.php, which mentions
Jan 7 on that page.   "SELECT PostGIS_Full_Version();" returns

"POSTGIS="2.0.0SVN" GEOS="3.3.0-CAPI-1.7.0" PROJ="Rel. 4.6.1, 21 August
2008" LIBXML="2.7.6" USE_STATS"

Where is the best location to get the latest postgis?  checkout via SVN?

I will give the hard upgrade method a try.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*



On Wed, Jan 25, 2012 at 5:40 PM, Paragon Corporation  wrote:

> **
>
>
>   >  I unfortunately have to move my Postgres/PostGIS database to a new
> Windows server.  My current (source) db is Postgres 9.0.2 with PostGIS
> 2.0.0SVN (I believe from Jan 2011.)
>  January 2011 is a bit old or do you mean 2012?
>
>
>  >  The new (destination) db will likely be Postgres 9.1.2.  It seems
> like I can use pg_dump to go from 9.0.2 to 9.1.2, as long as I use the 9.1
> version of pg_dump.  Is that true?
> Correct -- I do it alll tthe time.
>
>  >   For PostGIS 2.0, I plan to install the same version on both the
> source and destination db.  I'll do this before importing from pg_dump.  I
> can then update PostGIS on the new db after the migration is complete.
>  Does that make sense?  or should I install a newer version of PostGIS
> immediately?
>
> Depends how old your version is -- if its really 2011, I'm not sure our
> upgrade script can handle that far back cleanly.  You can try.
>
> I think your safest bet though is to:
>
> 1) Install the newest version of binaries on PostgreSQL 9.1
> 2) Create a new db with the latest PostGIS 2.0 experimental builds
> 3) Use hard upgrade process.
>
> http://www.postgis.org/documentation/manual-svn/postgis_installation.html#hard_upgrade
> (Note: The windows upgrade link for windows specific instructions) (
> http://trac.osgeo.org/postgis/wiki/UsersWikiWinUpgrade )
>
> If we are talking January 2012 -- then
> 1 ) Install new binaries
> 2) Restore your backup -- some things might fail to restore, that's okay
> 3) Rung the postgis_upgrade_20_minor and
> rtpostgis_upgrade_20_minor.sql
>
> in the share/contrib folder of the experimental zip folder.
>
> Note: Some changes that require a dump restore have happened since we
> distributed the last experimental -- but those changes don't effect raster
> functionality
>  and I plan to release a new build soon once I get my make check working
> again.
>
> Hope that helps,
> Regina
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] migration/upgrade advice

[John Callahan]

I unfortunately have to move my Postgres/PostGIS database to a new Windows
server.  My current (source) db is Postgres 9.0.2 with PostGIS 2.0.0SVN (I
believe from Jan 2011.)

The new (destination) db will likely be Postgres 9.1.2.  It seems like I
can use pg_dump to go from 9.0.2 to 9.1.2, as long as I use the 9.1 version
of pg_dump.  Is that true?

For PostGIS 2.0, I plan to install the same version on both the source and
destination db.  I'll do this before importing from pg_dump.  I can then
update PostGIS on the new db after the migration is complete.  Does that
make sense?  or should I install a newer version of PostGIS immediately?


Thanks for any advice you may have.  I haven't had to move a pg database
before.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] adding csv tables

[John Callahan]

Of course!  Putting the csv file on the same machine as the database would
help.  Single quotes with forward slashes work fine.Sorry for wasting
your time...

- John



On Wed, Aug 10, 2011 at 3:33 PM, Charles Galpin  wrote:

> I use single forward slashes just fine, but just in case, this has to be
> run on the same machine as the server afaik.
>
> charles
>
> On Aug 10, 2011, at 3:28 PM, John Callahan wrote:
>
> Sorry if this is more of a postgres question than postgis...
>
> I'm trying to bring in a CSV table into my postgis database.  I'm using the
> COPY command but kepe getting the error "No such file or directory."  I'm on
> Windows and my guess is the error is caused by incorrect syntax.  The online
> docs mentions that Windows users might need to use the E'' string syntax.
>  What is that and how is it used?  I can't find any good examples. Is the
> following correct?  Thanks.
>
> COPY mypgtable FROM 'C:\\data\\projectname\\locations.csv' CSV HEADER;
>
>
> - John
>
> ***
> John Callahan, Research Scientist
> Delaware Geological Survey
> University of Delaware
> URL: http://www.dgs.udel.edu
> ***
>  ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] adding csv tables

[John Callahan]

Sorry if this is more of a postgres question than postgis...

I'm trying to bring in a CSV table into my postgis database.  I'm using the
COPY command but kepe getting the error "No such file or directory."  I'm on
Windows and my guess is the error is caused by incorrect syntax.  The online
docs mentions that Windows users might need to use the E'' string syntax.
 What is that and how is it used?  I can't find any good examples. Is the
following correct?  Thanks.

COPY mypgtable FROM 'C:\\data\\projectname\\locations.csv' CSV HEADER;


- John

***
John Callahan, Research Scientist
Delaware Geological Survey
University of Delaware
URL: http://www.dgs.udel.edu
***
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] converting to lat long

[John Callahan]

Looks like you already have lat/long coordinates and don't need to use
ST_Transform.  Make sure you know if the original values are in 'decimal
degrees' or 'degrees minutes seconds'.  If in DD, just divide your values by
100 which should be easy in SQL.  If in DMS, then you need to parse the data
first.  I'm not sure how to do that in SQL but sure it's possible.

- John



On Tue, Jul 12, 2011 at 8:58 AM, Yamini Singh  wrote:

>  *Hi All,*
>
> *
> *
>
> *I have a column 'geocode' in a table which has attributes like 2329/4727
> now I would like to convert these **attributes **in another column to
> 'lat' and 'long'. For example, 'Lat' column will have **attribute '**23.29'
> and 'long' column will have **attribute ** '47.27'.*
>
> *Is there a way through which this can be converted automatically by a
> query or so.*
>
> *
> *
>
> *Looking fwd..*
>
> * *
>
> *Thanks*
>
> *YJ*
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] gridded data

[John Callahan]

This does sound like a job for PostGIS raster in v2.0.  I hope to do the
same in the near future.  You can query values (ST_Value) and perform other
functions listed here

http://postgis.refractions.net/documentation/manual-svn/RT_reference.html

Generating maps from PostGIS rasters would come through GDAL (MapServer,
Quantum GIS) and others.

- John

***
John Callahan, Research Scientist
Delaware Geological Survey
University of Delaware
URL: http://www.dgs.udel.edu
***


On Mon, May 9, 2011 at 11:28 AM, Stephen Crawford  wrote:

> Hi,
>
> How do I best use gridded data in postgres-postgis (I've only used vector
> data before)?
>
> I want to use daily weather variables that are in a grid for USA.  I would
> want store the daily variables ( a new grid each day) so I can have a
> database that will allow the client to send a point lat/lon and then return
> a calculation based on a few days worth of two or three variables.
>
> Do I store each day (or variable) as a new table?  How does a grid get
> stored in postgres?  Is this a job for PostGIS Raster (though I do not want
> to produce images, just queries at a point)?
>
> Any thoughts on this will be greatly appreciated.
>
> Thanks,
> Steve
>
>
> --
> Stephen Crawford
> Center for Environmental Informatics
> The Pennsylvania State University
> src...@psu.edu
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis visual client

[John Callahan]

It's working now.  Great.  Thanks for the help, and the plugin.   I had an
older version of .NET 2.0 on my machine (WinXP x64).  Upgrading to .NET
3.0/3.5 did the trick.

And thanks for the discussion.  With the availability of this plugin
(PostGISViewer), OpenJump, several QGIS plugins, and the PostGIS Terminal
from Stefan, it makes my original question seem silly.

- John



On Fri, Feb 11, 2011 at 10:59 AM, j.rolland  wrote:

>
> John,
>
> MapWindow6 or .NET 4.0 don't need to be installed,
>
> 1) just download
> http://dl.free.fr/icO8LcHoa PostGISViewer  and decompress it like follow :
>
> http://old.nabble.com/file/p30902546/directory_postgisviewer.jpg
>
> 2) define the binaries directory in PgAdmin III Preferences :
>
> http://old.nabble.com/file/p30902546/preferences_pgadminIII.jpg
>
> you can see that the PostGisViewer directory is in C:\Program
> Files\PostgreSQL\9.0\bin
>
> 3) put the lines that follow in the plugins.ini file
>
> ;
> ; PgViewer (Windows)
> ;
> [Separator]
> Title=PostGisViewer
> Command="$$PGBINDIR\PostGisViewer\PGViewer.exe"  "host=$$HOSTNAME"
> "port=$$PORT" "username=$$USERNAME" "password=$$PASSWORD"
> "database=$$DATABASE" "schema=$$SCHEMA"  "table=$$TABLE"
> Description=PostGIS Viewer
> KeyFile=$$PGBINDIR\PostGisViewer\PGViewer.exe
> Platform=windows
> ServerType=postgresql
> Database=Yes
> ;AppliesTo=database
> SetPassword=Yes
>
> 4) When you start again PgAdmin you must see PostGisViewer in the Plugins
> Menu.
>
>
> I am using PostgreSQL 9.0 and PgAdmin III 1.12.1 , but i had also tested my
> plugin with Postgresql 8.2 /8.4 and PgAdmin III 1.10
>
>
> regards,
> Jérôme Rolland
>
>
> John Callahan wrote:
> >
> > Thanks for the reference.  I love the idea of a map viewer plugin for
> > pgAdmin.   I wasn't aware of this plugin.  Unfortunately, I could not get
> > it
> > to work through pgAdmin III 1.12 (error: "pgviewer encountered a problem
> > and
> > needs to close")   Off-hand, does it require MapWindow6 or .NET 4.0 to be
> > installed?
> >
> > - John
> >
> > **
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > **
> >
> >
> > On Thu, Feb 10, 2011 at 4:51 AM, j.rolland  wrote:
> >
> >>
> >> John,
> >>
> >>
> >> I developed a plugin for PgAdmin III to visualize PostGIS data.
> >> Go to the following link
> >>
> >>
> http://ageoguy.blogspot.com/2010/06/plugin-pgadmin-iii-postgisviewer-suite.html
> >> postgisviewer a plugin for PgAdmin III
> >>  on my blog where you can download it and you will find a tutorial (in
> >> french) to install and use it.
> >>
> >> You can also consult this link
> >>
> >>
> http://www.postgresonline.com/journal/archives/180-pgAdmin113plugins_postgis.html
> >> Postgresonline journal pgAdmin III plugins postgis
> >>
> >> Regards,
> >> Jérôme Rolland
> >>
> >>
> >> John Callahan wrote:
> >> >
> >> > I'm sure this has been asked many times but haven't found a definitive
> >> or
> >> > consensus answer...
> >> >
> >> > Is there a postgis client that supports spatial, ad hoc queries and
> >> > returns
> >> > those results to a map?  This would include simple (and more complex)
> >> > SELECT
> >> > statements but also other queries with spatial results, such as
> finding
> >> > nearest neighbors, intersect, union, etc...
> >> >
> >> > The best idea I found was to create a view from the ad hoc query and
> >> then
> >> > use a desktop GIS to display that view.  You'd need to update the view
> >> > (and
> >> > refresh the GIS screen) to run a new query.   I know QGIS (and other
> >> FOSS
> >> > GIS packages) allows you to create a definition query (a where clause
> >> to
> >> > subset the layer) and does support database views.  I haven't seen a
> >> place
> >> > where QGIS supports ad hoc queries.  Searching the web, I did find
> >> > references to some work done using OpenMap libraries back in 2004, and
> >> the
> >> > mezoGIS package from around 2005/2006.
> >> >
> >> > Does anyone 

Re: [postgis-users] postgis visual client

[John Callahan]

Thanks for the reference.  I love the idea of a map viewer plugin for
pgAdmin.   I wasn't aware of this plugin.  Unfortunately, I could not get it
to work through pgAdmin III 1.12 (error: "pgviewer encountered a problem and
needs to close")   Off-hand, does it require MapWindow6 or .NET 4.0 to be
installed?

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**


On Thu, Feb 10, 2011 at 4:51 AM, j.rolland  wrote:

>
> John,
>
>
> I developed a plugin for PgAdmin III to visualize PostGIS data.
> Go to the following link
>
> http://ageoguy.blogspot.com/2010/06/plugin-pgadmin-iii-postgisviewer-suite.html
> postgisviewer a plugin for PgAdmin III
>  on my blog where you can download it and you will find a tutorial (in
> french) to install and use it.
>
> You can also consult this link
>
> http://www.postgresonline.com/journal/archives/180-pgAdmin113plugins_postgis.html
> Postgresonline journal pgAdmin III plugins postgis
>
> Regards,
> Jérôme Rolland
>
>
> John Callahan wrote:
> >
> > I'm sure this has been asked many times but haven't found a definitive or
> > consensus answer...
> >
> > Is there a postgis client that supports spatial, ad hoc queries and
> > returns
> > those results to a map?  This would include simple (and more complex)
> > SELECT
> > statements but also other queries with spatial results, such as finding
> > nearest neighbors, intersect, union, etc...
> >
> > The best idea I found was to create a view from the ad hoc query and then
> > use a desktop GIS to display that view.  You'd need to update the view
> > (and
> > refresh the GIS screen) to run a new query.   I know QGIS (and other FOSS
> > GIS packages) allows you to create a definition query (a where clause to
> > subset the layer) and does support database views.  I haven't seen a
> place
> > where QGIS supports ad hoc queries.  Searching the web, I did find
> > references to some work done using OpenMap libraries back in 2004, and
> the
> > mezoGIS package from around 2005/2006.
> >
> > Does anyone know of a GUI tool to use, hopefully one that works for
> > Postgres
> > 9 and PostGIS 2?  Thanks.
> >
> > - John
> >
> > PS - Thanks for the recommendations on the PostGIS in Action book.  I
> just
> > purchased it and looking forward to learning what I can.
> >
> > **
> > John Callahan, Research Scientist
> > Delaware Geological Survey, University of Delaware
> > URL: http://www.dgs.udel.edu
> > **
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/postgis-visual-client-tp30875438p30890812.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis visual client

[John Callahan]

Thank you Stefan.  Yes, I would be interested in testing your application.
Looks interesting.

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**


On Wed, Feb 9, 2011 at 8:36 PM, Stefan Keller  wrote:

> There are three different prototypes called "PostGIS Terminal" around for a
> web based client with a "fixed" PostGIS geodatabase below.
>
> The original PostGIS-Terminal probably is
> http://openlayers-buch.de/beispiele/chapter-09/postgis-terminal.html (its
> german) then there is http://www.postgisonline.org/  .
>
> I've enhanced theses ideas: http://www.gis.hsr.ch/wiki/PostGIS_Terminal .
> It's still in an early stage but I can send the code (html, JavaScript, PHP)
> to anyone who is interested to become a beta tester :->
>  Yours, S.
> 2011/2/9 Michaël Michaud 
>
>  Hi,
>>
>> Le 08/02/2011 19:21, Paragon Corporation a écrit :
>>
>> John,
>> We like using OpenJump for that -- here is a quick tutorial we wrote up on
>> doing Ad hoc queries with it.
>>
>>
>> http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html
>>
>> It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to
>> download the latest JDBC drivers or set your bytea_output to escape - both
>> are documented in the FAQ
>>
>> http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352
>>
>>
>> Thanks for the good documentation,
>> Would like to mention an undocumented feature coming with the last
>> OpenJUMP release.
>>
>> You can copy the "fence" geometry or the "view extent" in your query  :
>>
>>
>>
>> button View (Vue in the screenshot) will copy "${view:-1}" where ever you
>> want in the query (replaced by view extent at execution time)
>> button Fence (Cadre in the screenshot) will copy "${fence:-1}" where ever
>> you want in the query (replaced by the fence geometry at execution time)
>>
>> It may help to download small parts of large datasets.
>>
>> Michaël
>>
>>
>> Leo
>> http://www.postgis.us
>>
>>
>>  --
>> *From:* postgis-users-boun...@postgis.refractions.net [
>> mailto:postgis-users-boun...@postgis.refractions.net]
>> *On Behalf Of *John Callahan
>> *Sent:* Tuesday, February 08, 2011 12:25 PM
>> *To:* PostGIS Users Discussion
>> *Subject:* [postgis-users] postgis visual client
>>
>> I'm sure this has been asked many times but haven't found a definitive or
>> consensus answer...
>>
>> Is there a postgis client that supports spatial, ad hoc queries and
>> returns those results to a map?  This would include simple (and more
>> complex) SELECT statements but also other queries with spatial results, such
>> as finding nearest neighbors, intersect, union, etc...
>>
>> The best idea I found was to create a view from the ad hoc query and then
>> use a desktop GIS to display that view.  You'd need to update the view (and
>> refresh the GIS screen) to run a new query.   I know QGIS (and other FOSS
>> GIS packages) allows you to create a definition query (a where clause to
>> subset the layer) and does support database views.  I haven't seen a place
>> where QGIS supports ad hoc queries.  Searching the web, I did find
>> references to some work done using OpenMap libraries back in 2004, and the
>> mezoGIS package from around 2005/2006.
>>
>> Does anyone know of a GUI tool to use, hopefully one that works for
>> Postgres 9 and PostGIS 2?  Thanks.
>>
>> - John
>>
>> PS - Thanks for the recommendations on the PostGIS in Action book.  I just
>> purchased it and looking forward to learning what I can.
>>
>> **
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> **
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
<>___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgis visual client

[John Callahan]

Thanks for the responses.  What you describe in your write up with OpenJump
is exactly what I'm trying to do.  Also, in addition to the QGIS plugins
strk mentions, I just found two more possibilities: PgQuery for QGIS and
Postgis SQL Editor.  Looks like there are some good options available.
Thanks again.

- John



On Tue, Feb 8, 2011 at 1:21 PM, Paragon Corporation  wrote:

>  John,
> We like using OpenJump for that -- here is a quick tutorial we wrote up on
> doing Ad hoc queries with it.
>
>
> http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html
>
> It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to
> download the latest JDBC drivers or set your bytea_output to escape - both
> are documented in the FAQ
>
> http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352
>
> Leo
> http://www.postgis.us
>
>
>  --
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John
> Callahan
> *Sent:* Tuesday, February 08, 2011 12:25 PM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] postgis visual client
>
> I'm sure this has been asked many times but haven't found a definitive or
> consensus answer...
>
> Is there a postgis client that supports spatial, ad hoc queries and returns
> those results to a map?  This would include simple (and more complex) SELECT
> statements but also other queries with spatial results, such as finding
> nearest neighbors, intersect, union, etc...
>
> The best idea I found was to create a view from the ad hoc query and then
> use a desktop GIS to display that view.  You'd need to update the view (and
> refresh the GIS screen) to run a new query.   I know QGIS (and other FOSS
> GIS packages) allows you to create a definition query (a where clause to
> subset the layer) and does support database views.  I haven't seen a place
> where QGIS supports ad hoc queries.  Searching the web, I did find
> references to some work done using OpenMap libraries back in 2004, and the
> mezoGIS package from around 2005/2006.
>
> Does anyone know of a GUI tool to use, hopefully one that works for
> Postgres 9 and PostGIS 2?  Thanks.
>
> - John
>
> PS - Thanks for the recommendations on the PostGIS in Action book.  I just
> purchased it and looking forward to learning what I can.
>
> **
> John Callahan, Research Scientist
> Delaware Geological Survey, University of Delaware
> URL: http://www.dgs.udel.edu
> **
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] postgis visual client

[John Callahan]

I'm sure this has been asked many times but haven't found a definitive or
consensus answer...

Is there a postgis client that supports spatial, ad hoc queries and returns
those results to a map?  This would include simple (and more complex) SELECT
statements but also other queries with spatial results, such as finding
nearest neighbors, intersect, union, etc...

The best idea I found was to create a view from the ad hoc query and then
use a desktop GIS to display that view.  You'd need to update the view (and
refresh the GIS screen) to run a new query.   I know QGIS (and other FOSS
GIS packages) allows you to create a definition query (a where clause to
subset the layer) and does support database views.  I haven't seen a place
where QGIS supports ad hoc queries.  Searching the web, I did find
references to some work done using OpenMap libraries back in 2004, and the
mezoGIS package from around 2005/2006.

Does anyone know of a GUI tool to use, hopefully one that works for Postgres
9 and PostGIS 2?  Thanks.

- John

PS - Thanks for the recommendations on the PostGIS in Action book.  I just
purchased it and looking forward to learning what I can.

******
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] migrating tables to postgis

[John Callahan]

Good point.  Thanks for the tip.

- John



On Sun, Jan 23, 2011 at 5:50 PM, Etienne Bellemare wrote:

> Little detail, it's not really mandatory to include "Table1"."DGSID" as
> dgsid2 as you do the join on it. It should be the same then DGSID.
>
> Etienne
>
>
> On Fri, Jan 21, 2011 at 1:38 PM, John Callahan wrote:
>
>> Thank you for all your help.   In the end, it turns out to be relatively
>> simple (as long as you know who to ask...)   For anyone interested, here are
>> the steps I performed to display these tables together in QGIS.
>>
>>
>> 1. Add a geometry column to the table that contains the coordinates, which
>> is my station inventory table, Table1.
>>
>> SELECT AddGeometryColumn('Table1','the_geom', 26918,'POINT', 2);
>> UPDATE Table1 SET the_geom = ST_SetSRID(ST_MakePoint(EASTING,NORTHING),
>> 26918);
>>
>>
>> 2. Add a primary key field (of integer type) to my project data table
>> (Table2) that will be used as the primary key in QGIS.
>>
>> ALTER TABLE "Table2" ADD COLUMN id SERIAL PRIMARY KEY;
>>
>>
>> 3. Create a view containing all records in my project data table and the
>> matching geometry (and a few other fields I need) in the station inventory
>> table.  "DGSID" is the common field between them, which I had to give an
>> alias to since it had the same name in both tables.
>>
>> CREATE VIEW "siteview" AS
>> SELECT
>>   "Table2".*,
>>   "Table1"."DGSID" as dgsid2,
>>   "Table1"."EASTING",
>>   "Table1"."NORTHING",
>>   "Table1"."the_geom"
>> FROM
>>   public."Table2",
>>   public."Table1"
>> WHERE
>>   "Table1"."DGSID" = "Table2"."DGSID"
>> ORDER BY
>>   "Table2"."DGSID" ASC;
>>
>>
>> 4.  Manually add a record for the view in the geometry_columns table.
>>
>> INSERT INTO geometry_columns(f_table_catalog, f_table_schema,
>> f_table_name, f_geometry_column, coord_dimension, srid, "type")
>> VALUES ('', 'public', 'siteview', 'the_geom', 2, 26918, 'POINT');
>>
>>
>> That's it.  It's working beautifully.  Thanks again.
>>
>> - John
>>
>> **
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> **
>>
>>
>> On Wed, Jan 19, 2011 at 12:20 PM, MarkW  wrote:
>>
>>> Views are fine in Qgis for me, but in my experience you'll need to
>>> manually add a record to the GeometryColumns table, and include a suitable
>>> unique ID field into the view or Qgis will complain.
>>>
>>> I think views are what you want based on your description. I think the
>>> table structure qualifier was because you said "etc" without giving all
>>> fields from each table, and a one-to-many relationship (or other) wasn't all
>>> that clear in your first message. My guess. But yeah, it's easier if you
>>> give the relationship between tables and a few rows of sample data (in, out)
>>> can't hurt in communicating.
>>>
>>> Finally, Etienne's suggestion was better - my impression (from the
>>> PostGIS in Action book which I recommend by the way!) gives the example of
>>> ST_point for your purpose and I've had an easier time using it with column
>>> names than ST_GeomFromText.
>>>
>>> Mark
>>>
>>>
>>> On Wed, Jan 19, 2011 at 11:18 AM, John Callahan 
>>> wrote:
>>>
>>>> Thanks for your response Rob. Looks good.  I'll give this a try.
>>>>
>>>> I cannot merge everything into one table.  Our station inventory table
>>>> contains only the basics/metadata about each station.  As other projects
>>>> arise, they each have their own tables containing data observations for 
>>>> that
>>>> project only.  There are many project tables with all types of data.
>>>>
>>>> As an aside, for future questions, what kind of table information would
>>>> you (the list) need in order to provide support?  Would you need column
>>>> types (string, numeric), or some sample data?   Thanks.
>>>>
>>>&g

Re: [postgis-users] migrating tables to postgis

[John Callahan]

Thank you for all your help.   In the end, it turns out to be relatively
simple (as long as you know who to ask...)   For anyone interested, here are
the steps I performed to display these tables together in QGIS.


1. Add a geometry column to the table that contains the coordinates, which
is my station inventory table, Table1.

SELECT AddGeometryColumn('Table1','the_geom', 26918,'POINT', 2);
UPDATE Table1 SET the_geom = ST_SetSRID(ST_MakePoint(EASTING,NORTHING),
26918);


2. Add a primary key field (of integer type) to my project data table
(Table2) that will be used as the primary key in QGIS.

ALTER TABLE "Table2" ADD COLUMN id SERIAL PRIMARY KEY;


3. Create a view containing all records in my project data table and the
matching geometry (and a few other fields I need) in the station inventory
table.  "DGSID" is the common field between them, which I had to give an
alias to since it had the same name in both tables.

CREATE VIEW "siteview" AS
SELECT
  "Table2".*,
  "Table1"."DGSID" as dgsid2,
  "Table1"."EASTING",
  "Table1"."NORTHING",
  "Table1"."the_geom"
FROM
  public."Table2",
  public."Table1"
WHERE
  "Table1"."DGSID" = "Table2"."DGSID"
ORDER BY
  "Table2"."DGSID" ASC;


4.  Manually add a record for the view in the geometry_columns table.

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, "type")
VALUES ('', 'public', 'siteview', 'the_geom', 2, 26918, 'POINT');


That's it.  It's working beautifully.  Thanks again.

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**


On Wed, Jan 19, 2011 at 12:20 PM, MarkW  wrote:

> Views are fine in Qgis for me, but in my experience you'll need to manually
> add a record to the GeometryColumns table, and include a suitable unique ID
> field into the view or Qgis will complain.
>
> I think views are what you want based on your description. I think the
> table structure qualifier was because you said "etc" without giving all
> fields from each table, and a one-to-many relationship (or other) wasn't all
> that clear in your first message. My guess. But yeah, it's easier if you
> give the relationship between tables and a few rows of sample data (in, out)
> can't hurt in communicating.
>
> Finally, Etienne's suggestion was better - my impression (from the PostGIS
> in Action book which I recommend by the way!) gives the example of ST_point
> for your purpose and I've had an easier time using it with column names than
> ST_GeomFromText.
>
> Mark
>
>
> On Wed, Jan 19, 2011 at 11:18 AM, John Callahan wrote:
>
>> Thanks for your response Rob. Looks good.  I'll give this a try.
>>
>> I cannot merge everything into one table.  Our station inventory table
>> contains only the basics/metadata about each station.  As other projects
>> arise, they each have their own tables containing data observations for that
>> project only.  There are many project tables with all types of data.
>>
>> As an aside, for future questions, what kind of table information would
>> you (the list) need in order to provide support?  Would you need column
>> types (string, numeric), or some sample data?   Thanks.
>>
>>
>> - John
>>
>> **
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> **
>>
>>
>> On Wed, Jan 19, 2011 at 10:56 AM,  wrote:
>>
>>>  Hi John:
>>>
>>> Given your stated table structure**, a query like the following should
>>> give you the records you want.
>>>
>>> SELECT t2.*, t1.geometry_column  --substitute your newly created geometry
>>> column for "geometry_column"
>>> FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid);
>>>
>>> The "LEFT JOIN" specifies that you want all rows from table2 and just the
>>> matching rows from table1.
>>>
>>> **It's difficult to predict success of any proposed solution without more
>>> info on the structure and content of your two tables.
>>>
>>> Have you considered merging the data into one table, allowing null valu

Re: [postgis-users] migrating tables to postgis

[John Callahan]

Thanks for your response Rob. Looks good.  I'll give this a try.

I cannot merge everything into one table.  Our station inventory table
contains only the basics/metadata about each station.  As other projects
arise, they each have their own tables containing data observations for that
project only.  There are many project tables with all types of data.

As an aside, for future questions, what kind of table information would you
(the list) need in order to provide support?  Would you need column types
(string, numeric), or some sample data?   Thanks.

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**


On Wed, Jan 19, 2011 at 10:56 AM,  wrote:

>  Hi John:
>
> Given your stated table structure**, a query like the following should give
> you the records you want.
>
> SELECT t2.*, t1.geometry_column  --substitute your newly created geometry
> column for "geometry_column"
> FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid);
>
> The "LEFT JOIN" specifies that you want all rows from table2 and just the
> matching rows from table1.
>
> **It's difficult to predict success of any proposed solution without more
> info on the structure and content of your two tables.
>
> Have you considered merging the data into one table, allowing null values
> for the table2 attributes? You would then be able to select specific records
> without performing a join. If you've already created your table1 geometry
> column, you can quickly generate a "master" table or view (don't know if
> QGIS will recognize a view--might have to be a table) by:
>
>  CREATE TABLE stations AS  --or CREATE VIEW stations AS
> SELECT t2.*, t1.*
> FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid);
>
> Hope that helps.
>
> Cheers,
> Rob
>
>  ------
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John
> Callahan
> *Sent:* Wednesday, January 19, 2011 9:30 AM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] migrating tables to postgis
>
> Thanks Mark.  This gives me a direction to go.  I have the correct SRS, and
> your other points are valuable.  ST_GeomFromText is really what I was
> missing.  Thanks.
>
> The fact that I have two data tables to combine, would a View help here?
> Or somehow modify the SELECT statements that would go into the
> ST_GeomFromText function?  (the examples only show hard-coded coordinates
> but I'm hoping a SELECT statement can provide input.)
>
> - John
>
>
>
> On Tue, Jan 18, 2011 at 4:20 PM, MarkW  wrote:
>
>> Now that you have data in columns in Postgresql, you can use SQL
>> statements to create your spatial data. Here are the steps:
>> 1) identify your SRID/ spatial reference system
>> 2) create geometries by passing your x and y with a spatial ref to the
>> right function, and
>> 3) add a row to the geometry_columns table so that other applications can
>> more easily see the spatial data.
>>
>> 1) It's much easier if you can match your coordinate system to the right
>> UTM srids in the spatial_ref_sys table; see spatialreference.org for
>> help.
>> 2) See this function:
>> http://www.postgis.org/docs/ST_GeomFromText.html
>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out )
>> 3)
>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
>> (but also see the help under 'AddGeometryColumn (
>> http://postgis.refractions.net/docs/AddGeometryColumn.html)
>>
>> Hope this helps.
>>
>> Mark
>>
>>
>>   On Tue, Jan 18, 2011 at 3:37 PM, John Callahan 
>> wrote:
>>
>>>  I apologize for asking what seems like an easy question.   I'm really
>>> just getting started with PostGIS and not sure which way to go here.   I
>>> have a two tables in MS Access format.  They are:
>>>
>>> Table1: StationID, easting, northing, elevation, etc...
>>> Table2: StationID, data values...
>>>
>>> Table1 is basically an inventory of all our stations.  Table2 is a subset
>>> that includes only stations we have certain data for.   How would I convert
>>> these into a point data set (of Table2 stations) in PostGIS?   I was able to
>>> copy the tables from Access into Postgres. Where would I go from here?
>>> Maybe OGR would help going directly from Access (or text exports of Access)
>

Re: [postgis-users] migrating tables to postgis

[John Callahan]

Thank you Etienne.  That may be the way to go.

The problem is that one table has the necessary attributes and another table
has the x,y coordinates.  They need to be separate tables.  Also, I'm using
EPSG:26918 (UTM Z18 meters).  Basic structures of both tables are:

Table1: StationID, easting, northing, elevation, owner, date (our complete
inventory table)
Table2: StationID, value1, value2, value3, etc...  (a data table with
observations)


Table2 gets updated on a more frequent basis, daily to weekly.  Table1 only
a few times per year.

Could I add a geom column to Table1 and then create a view that brings in
the data values I want to map?  I am not sure if packages such as QGIS,
MapServer, etc.., can read the view as a spatial class.  Or maybe create a
third table that brings in fields from Table1 and Table2, and then adds a
geom column?

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**


On Wed, Jan 19, 2011 at 10:28 AM, Etienne Bellemare wrote:

> You should probably use something like this (let's say your coordinates are
> in the x and y columns [also replace  and ]):
>
> SELECT AddGeometryColumn(' ','the_geom', ,'POINT', 2);
> UPDATE   SET the_geom = ST_SetSRID(ST_MakePoint(x,y), );
> --replace x and y if it's not the good columns
>
> For the view (you haven't provided much information), it depends of your
> table structure, if they are the same, if when you update one you're likely
> to update the other and if they aren't too big, then maybe it should be
> merged in a single table. Otherwise, a view could be usefull if you need to
> view these simultaneously.
>
> Etienne
>
>
> On Wed, Jan 19, 2011 at 9:30 AM, John Callahan wrote:
>
>> Thanks Mark.  This gives me a direction to go.  I have the correct SRS,
>> and your other points are valuable.  ST_GeomFromText is really what I was
>> missing.  Thanks.
>>
>> The fact that I have two data tables to combine, would a View help here?
>> Or somehow modify the SELECT statements that would go into the
>> ST_GeomFromText function?  (the examples only show hard-coded coordinates
>> but I'm hoping a SELECT statement can provide input.)
>>
>> - John
>>
>>
>>
>> On Tue, Jan 18, 2011 at 4:20 PM, MarkW  wrote:
>>
>>> Now that you have data in columns in Postgresql, you can use SQL
>>> statements to create your spatial data. Here are the steps:
>>> 1) identify your SRID/ spatial reference system
>>> 2) create geometries by passing your x and y with a spatial ref to the
>>> right function, and
>>> 3) add a row to the geometry_columns table so that other applications can
>>> more easily see the spatial data.
>>>
>>> 1) It's much easier if you can match your coordinate system to the right
>>> UTM srids in the spatial_ref_sys table; see spatialreference.org for
>>> help.
>>> 2) See this function:
>>> http://www.postgis.org/docs/ST_GeomFromText.html
>>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out
>>> )
>>> 3)
>>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
>>> (but also see the help under 'AddGeometryColumn (
>>> http://postgis.refractions.net/docs/AddGeometryColumn.html)
>>>
>>> Hope this helps.
>>>
>>> Mark
>>>
>>>
>>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan 
>>> wrote:
>>>
>>>> I apologize for asking what seems like an easy question.   I'm really
>>>> just getting started with PostGIS and not sure which way to go here.   I
>>>> have a two tables in MS Access format.  They are:
>>>>
>>>> Table1: StationID, easting, northing, elevation, etc...
>>>> Table2: StationID, data values...
>>>>
>>>> Table1 is basically an inventory of all our stations.  Table2 is a
>>>> subset that includes only stations we have certain data for.   How would I
>>>> convert these into a point data set (of Table2 stations) in PostGIS?   I 
>>>> was
>>>> able to copy the tables from Access into Postgres. Where would I go from
>>>> here?Maybe OGR would help going directly from Access (or text exports 
>>>> of
>>>> Access) into PostGIS?
>>>>
>>>> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum
>>>> GIS for view

Re: [postgis-users] migrating tables to postgis

[John Callahan]

Thanks Mark.  This gives me a direction to go.  I have the correct SRS, and
your other points are valuable.  ST_GeomFromText is really what I was
missing.  Thanks.

The fact that I have two data tables to combine, would a View help here?  Or
somehow modify the SELECT statements that would go into the ST_GeomFromText
function?  (the examples only show hard-coded coordinates but I'm hoping a
SELECT statement can provide input.)

- John



On Tue, Jan 18, 2011 at 4:20 PM, MarkW  wrote:

> Now that you have data in columns in Postgresql, you can use SQL statements
> to create your spatial data. Here are the steps:
> 1) identify your SRID/ spatial reference system
> 2) create geometries by passing your x and y with a spatial ref to the
> right function, and
> 3) add a row to the geometry_columns table so that other applications can
> more easily see the spatial data.
>
> 1) It's much easier if you can match your coordinate system to the right
> UTM srids in the spatial_ref_sys table; see spatialreference.org for help.
> 2) See this function:
> http://www.postgis.org/docs/ST_GeomFromText.html
> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out )
> 3)
> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
> (but also see the help under 'AddGeometryColumn (
> http://postgis.refractions.net/docs/AddGeometryColumn.html)
>
> Hope this helps.
>
> Mark
>
>
> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan wrote:
>
>> I apologize for asking what seems like an easy question.   I'm really just
>> getting started with PostGIS and not sure which way to go here.   I have a
>> two tables in MS Access format.  They are:
>>
>> Table1: StationID, easting, northing, elevation, etc...
>> Table2: StationID, data values...
>>
>> Table1 is basically an inventory of all our stations.  Table2 is a subset
>> that includes only stations we have certain data for.   How would I convert
>> these into a point data set (of Table2 stations) in PostGIS?   I was able to
>> copy the tables from Access into Postgres. Where would I go from here?
>> Maybe OGR would help going directly from Access (or text exports of Access)
>> into PostGIS?
>>
>> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS
>> for viewing.  Thanks for any guidance.
>>
>> - John
>>
>> **
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> **
>>
>>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] migrating tables to postgis

[John Callahan]

I apologize for asking what seems like an easy question.   I'm really just
getting started with PostGIS and not sure which way to go here.   I have a
two tables in MS Access format.  They are:

Table1: StationID, easting, northing, elevation, etc...
Table2: StationID, data values...

Table1 is basically an inventory of all our stations.  Table2 is a subset
that includes only stations we have certain data for.   How would I convert
these into a point data set (of Table2 stations) in PostGIS?   I was able to
copy the tables from Access into Postgres. Where would I go from here?
Maybe OGR would help going directly from Access (or text exports of Access)
into PostGIS?

I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS
for viewing.  Thanks for any guidance.

- John

**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] postgres data tables package (ORACLE Formsreplacement?)

[John Callahan]

Thanks for all your responses.  MS Access was tried before my time and
didn't work out so well (not sure why.)  OpenOffice sounds like a great idea
(I use it quite often for typical office tasks) but we do use some views.
WaveMaker looks very interesting.   Thanks for the reference.

- John



On Thu, Aug 5, 2010 at 12:59 PM, Randall, Eric
wrote:

>  John,
>
> For the web solution I use WaveMaker with but haven't done anything too
> fancy with it yet. http://www.wavemaker.com/
> SQL Workbench might be another option.  http://www.sql-workbench.net/
>
> Eric
>
>
> Eric Randall
> GIS DB Admin/Analyst
> County of Erie
> 140 W 6th St
> Room 111
> Erie, PA 16501
>
> ph. 814-451-6063
> fx. 814-451-7000
>
> -Original Message-
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net]*on Behalf Of *John Callahan
> *Sent:* Thursday, August 05, 2010 9:54 AM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] postgres data tables package (ORACLE
> Formsreplacement?)
>
> Brief background: We're currently using Oracle for our main database.
> Nothing too big, 15 - 20 tables and the largest table has about 100K
> records.   We use Oracle Forms as a web-based option for inserting and
> updating records.
>
> I am using Postgres/PostGIS for a couple of other projects.  I would like
> to use Postgres for our main database as well.  However, I would need to
> come up with a solution for inserting/updating the data tables within our
> group (a replacement for Oracle Forms).  Preferably, a web-based solution
> (PHP, Python, etc...) but it could be a Windows desktop solution installed
> on each staff members machine.  Something that maintains the data integrity
> (e.g., forcing users to enter a date when Date is required, etc...)
>
> Does anyone know of a FOSS package that can be used to manage data tables
> within a Postgres database?   Updating data records is the highest priority
> since the built-in Admin tool can be used for managing users, creating new
> tables, etc..., and will be done only by a database manager.  Thanks for any
> advice or guidance you can provide.
>
>
> - John
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] postgres data tables package (ORACLE Forms replacement?)

[John Callahan]

Brief background: We're currently using Oracle for our main database.
Nothing too big, 15 - 20 tables and the largest table has about 100K
records.   We use Oracle Forms as a web-based option for inserting and
updating records.

I am using Postgres/PostGIS for a couple of other projects.  I would like to
use Postgres for our main database as well.  However, I would need to come
up with a solution for inserting/updating the data tables within our group
(a replacement for Oracle Forms).  Preferably, a web-based solution (PHP,
Python, etc...) but it could be a Windows desktop solution installed on each
staff members machine.  Something that maintains the data integrity (e.g.,
forcing users to enter a date when Date is required, etc...)

Does anyone know of a FOSS package that can be used to manage data tables
within a Postgres database?   Updating data records is the highest priority
since the built-in Admin tool can be used for managing users, creating new
tables, etc..., and will be done only by a database manager.  Thanks for any
advice or guidance you can provide.


- John
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] sync geometry with attributes

[John Callahan]

This is great!  Thank you.  I never thought about creating a view and 
pointing some of our apps directly to the view instead of the table.  I 
will give this a try.


I'm currently trying to move my organization away from Oracle and into 
Postgres/PostGIS.  Our Oracle person (who is really a geologist doing 
the best he can!) has always had problems synchronizing the coordinates 
and attributes. If I can offer a solution through PostGIS, that would 
certainly strengthen my position.  (Of course, the same could be done 
with Oracle, but he doesn't need to know that!)


- John


****
John Callahan
Geospatial Application Developer

Delaware Geological Survey
University of Delaware
227 Academy St, Newark DE 19716-7501
Tel: (302) 831-3584  


Email: [EMAIL PROTECTED]
http://www.dgs.udel.edu




Obe, Regina wrote:

John

There are two ways of dealing with this

1) Put in a trigger in your table so that when the geometry gets edited,
It updates your long = ST_X(the_geom)
and your lat = ST_Y(the_geom)

2) The way I find easier to deal if you don't edit those fields directly
is just to get rid of your
long lat fields and create a view

CREATE VIEW vwmytable
AS 
	SELECT gid, field1, field2, field3, ST_X(the_geom) as long,

ST_Y(the_geom) As lat
FROM mytable

 You can also put an update rule on your view so that if anyone tried to
edit the long, lat fields
then it will update the_geom field with something like

the_geom = CASE WHEN OLD.long <> NEW.long THEN ST_MakePoint(long, lat,
SRID(Old.the_geom)) ELSE NEW.the_geom END

Hope that helps,
R

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of John
Callahan
Sent: Friday, May 23, 2008 3:49 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] sync geometry with attributes

Please excuse my question if it seems too trivial.  I am just getting 
started with PostGIS and couldn't quite find what I was looking for.


For a point data set, it is common for us to store the lat/long (or 
easting/northing) as attributes in the table. (Some programs we have 
require this.)  However, when I edit the position through a GIS (like 
QGIS), the spatial location is altered but obviously not the 
attributes.  And when these attributes are altered in the table, the 
geometry is not updated, obviously.   Is there a way to keep these 
synchronized?  Is this built into PostGIS? 


Thanks for your help.  Even is you don't know how this could be done, is

it possible?

- John

****
John Callahan
Geospatial Application Developer

Delaware Geological Survey
University of Delaware
227 Academy St, Newark DE 19716-7501
Tel: (302) 831-3584  


Email: [EMAIL PROTECTED]
http://www.dgs.udel.edu


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
  

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] sync geometry with attributes

[John Callahan]

Please excuse my question if it seems too trivial.  I am just getting 
started with PostGIS and couldn't quite find what I was looking for.


For a point data set, it is common for us to store the lat/long (or 
easting/northing) as attributes in the table. (Some programs we have 
require this.)  However, when I edit the position through a GIS (like 
QGIS), the spatial location is altered but obviously not the 
attributes.  And when these attributes are altered in the table, the 
geometry is not updated, obviously.   Is there a way to keep these 
synchronized?  Is this built into PostGIS? 

Thanks for your help.  Even is you don't know how this could be done, is 
it possible?


- John

****
John Callahan
Geospatial Application Developer

Delaware Geological Survey
University of Delaware
227 Academy St, Newark DE 19716-7501
Tel: (302) 831-3584  


Email: [EMAIL PROTECTED]
http://www.dgs.udel.edu


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users