Postresql + PostGIS
511 views
Skip to first unread message
Simon
Sep 3, 2008, 7:18:18 AM9/3/08
to Portable GIS
Like I mentioned in my greeting message - had not had much time to
fiddle with this yet.
Noticed that you canrun PostgreSQL from the USB drive - how exactly
does this work - once you have it running, what next, how do you
connect to it?
Is PostGIS included on the USB stick?
fiddle with this yet.
Noticed that you canrun PostgreSQL from the USB drive - how exactly
does this work - once you have it running, what next, how do you
connect to it?
Is PostGIS included on the USB stick?
jo.k...@googlemail.com
Sep 4, 2008, 4:58:25 AM9/4/08
to Portable GIS
Hi Simon,
Yes, postgis is on the usb drive- my apologies the documentation about
connecting to it is not so good (non-existent in fact). This is partly
because all the information about running postgresql and postgis are
available elsewhere, and written far better than I ever could. For a
really good intro, get Tyler Mitchell's O Reilly book Web Mapping
Illustrated. The postgresql docs are here:
http://www.postgresql.org/docs/8.2/interactive/index.html. Anyhow,
here's the basics:
1: You can connect to postgresql at the command line with the command
psql, which is found in [driveletter]:\usbgis\apps\pgsql\bin, and the
default username and password are postgres/grespost. If you value your
security, you can change the username and password at the command
line, but it's easier to use phppgadmin, which is a web-based tool
(we'll need to come back to the command line fairly soon)
2: You access phppgadmin at http://localhost/phpPgAdmin-4.0.1/ login
with the standard username and password above. In the users section
you can create new users- you'll see my setup user in there as well-
don't delete that because the server won't start!
3: To make all databases spatially enabled, you need to load the
postgis templates into template1, which contains the set of tables
that get copied into any new databases by default. Otherwise you can
load the postgis tables into any new database manually. For
instructions on how to do this at the command line, look here:
http://postgis.refractions.net/documentation/, but where it says
"yourtestdatabase", substitute "template1". You can create new
databases in phppgadmin or at the command line.
4: Once you have a spatially enabled database, you can connect to it
with qgis or gvsig using the parameters host=localhost, username and
password as above, and your database name.
That's a very brief set of instructions, so if something doesn't
work, then let me know and I'll try and explain in more detail!
All the best
Jo
Yes, postgis is on the usb drive- my apologies the documentation about
connecting to it is not so good (non-existent in fact). This is partly
because all the information about running postgresql and postgis are
available elsewhere, and written far better than I ever could. For a
really good intro, get Tyler Mitchell's O Reilly book Web Mapping
Illustrated. The postgresql docs are here:
http://www.postgresql.org/docs/8.2/interactive/index.html. Anyhow,
here's the basics:
1: You can connect to postgresql at the command line with the command
psql, which is found in [driveletter]:\usbgis\apps\pgsql\bin, and the
default username and password are postgres/grespost. If you value your
security, you can change the username and password at the command
line, but it's easier to use phppgadmin, which is a web-based tool
(we'll need to come back to the command line fairly soon)
2: You access phppgadmin at http://localhost/phpPgAdmin-4.0.1/ login
with the standard username and password above. In the users section
you can create new users- you'll see my setup user in there as well-
don't delete that because the server won't start!
3: To make all databases spatially enabled, you need to load the
postgis templates into template1, which contains the set of tables
that get copied into any new databases by default. Otherwise you can
load the postgis tables into any new database manually. For
instructions on how to do this at the command line, look here:
http://postgis.refractions.net/documentation/, but where it says
"yourtestdatabase", substitute "template1". You can create new
databases in phppgadmin or at the command line.
4: Once you have a spatially enabled database, you can connect to it
with qgis or gvsig using the parameters host=localhost, username and
password as above, and your database name.
That's a very brief set of instructions, so if something doesn't
work, then let me know and I'll try and explain in more detail!
All the best
Jo
Simon
Sep 6, 2008, 5:46:34 AM9/6/08
to Portable GIS
Thanks for the detailed reply Jo - Id imagine that reply would benefit
more than just me.
Left my PtbGIS at work, not had time to try your suggestions. Was
wonderin, because im doing ArcGIS Server stuff on my work machine, I
have IIS up and running.
Will this not cause issues with trying to get to PgAdmin via LocalHost?
more than just me.
Left my PtbGIS at work, not had time to try your suggestions. Was
wonderin, because im doing ArcGIS Server stuff on my work machine, I
have IIS up and running.
Will this not cause issues with trying to get to PgAdmin via LocalHost?
Jo Cook
Sep 8, 2008, 4:36:44 AM9/8/08
to portab...@googlegroups.com
Hi Simon,
Yes, if you already have a webserver set up then you will have issues not only with phppgadmin, but with apache in general. If you can get away with setting your existing web server to start manually then you can just switch on the one you want, when you want it, and still refer to them both as localhost. Otherwise you will need to configure one web server or the other to use a different servername, IP address and port, which is a little beyond the scope of portable gis...
All the best
Jo
Yes, if you already have a webserver set up then you will have issues not only with phppgadmin, but with apache in general. If you can get away with setting your existing web server to start manually then you can just switch on the one you want, when you want it, and still refer to them both as localhost. Otherwise you will need to configure one web server or the other to use a different servername, IP address and port, which is a little beyond the scope of portable gis...
All the best
Jo
--
===================================
Jo Kaye (nee Cook)
Email: jo.k...@googlemail.com
Blog: www.archaeogeek.com/blog
Photos: www.flickr.com/photos/barryandjo
===================================
===================================
Jo Kaye (nee Cook)
Email: jo.k...@googlemail.com
Blog: www.archaeogeek.com/blog
Photos: www.flickr.com/photos/barryandjo
===================================
Alex
Oct 10, 2008, 1:27:42 AM10/10/08
to Portable GIS
Just wanted to point out that I had a hard time getting Postgres to
work and thought these tips would help others.
1. I had to install PortableApps: Portable Command Prompt - for some
reason the machine I was on didn't want to cd to the flash drive F:
2. In order to connect to the database via pgadmin III or psql I had
to specify the database as template1
Example:
psql -d template1 -U postgres
Alex
On Sep 4, 1:58 am, "jo.k.c...@googlemail.com"
work and thought these tips would help others.
1. I had to install PortableApps: Portable Command Prompt - for some
reason the machine I was on didn't want to cd to the flash drive F:
2. In order to connect to the database via pgadmin III or psql I had
to specify the database as template1
Example:
psql -d template1 -U postgres
Alex
On Sep 4, 1:58 am, "jo.k.c...@googlemail.com"
jo.k...@googlemail.com
Oct 30, 2008, 7:50:53 AM10/30/08
to Portable GIS
Hi Alex,
Firstly, sorry for the delay in responding, I am now back from my
hols.
Do you think these problems were related to a specific machine or more
general? I haven't come across a problem with the drive letters
before. Have you tried also creating a new database- does that side of
things now work OK?
Thanks
Jo
Firstly, sorry for the delay in responding, I am now back from my
hols.
Do you think these problems were related to a specific machine or more
general? I haven't come across a problem with the drive letters
before. Have you tried also creating a new database- does that side of
things now work OK?
Thanks
Jo
Alex Mandel
Nov 3, 2008, 4:46:14 PM11/3/08
to portab...@googlegroups.com
It's seems to be a general problem on the machines I've tested that the
built in windows command prompt doesn't like switching to a drive letter
assigned to a usb device. Not really sure why, but portableapps Command
Prompt Portable works fine.
built in windows command prompt doesn't like switching to a drive letter
assigned to a usb device. Not really sure why, but portableapps Command
Prompt Portable works fine.
Interacting with the database is fine, there's just the oddity of having
to specify template1 as the maintanence database, this really only
applies to using PgAdmin (Which runs from a portable drive no problem)
and doesn't influence the connection in psql once one makes their own
database.
I'm am interested in trying to roll Postgres into the Portable apps
implementation of XAMMP, they have a nice gui for starting and stopping
services which could also be used for other server type apps too.
If I get around to it I'll try to work PgAdmin into something easy to
install also. Right now it's an install on a desktop dragged to the
Portable apps folder which works fine and it menu launchable.
I'll start another thread to discuss maintanence and upgrades of apps
and working with the OSGeo Live project.
Thanks,
Alex
Jo Cook
Nov 4, 2008, 3:47:56 AM11/4/08
to portab...@googlegroups.com
Hi Alex,
See other the thread on the OSGeo list about aligning portable gis with portableapps...
I'm curious about this command prompt issue. Can you explain in a little more detail, as it's not something I have come across, or that anyone else has mentioned. When exactly does this happen- when you run the setup programmes? What error do you get?
Other than when you try and use the FWTools command line tools, there should be no need to manually run a command prompt to use the packages, so this is all very curious.
I have heard elsewhere that pgadmin works well on a usb stick, so I'm going to include it in the next release in preference to phppgadmin. But, whenever I use pgadmin (on whatever machine), I always have to specify the maintenance database to set up a new connection anyhow- isn't this normal?
One other issue- do you see much benefit in setting things up as services? A lot of people have asked about including postgresql within xampp, portableapp or not- it will be interesting to see how you get on with that!
All the best
Jo
See other the thread on the OSGeo list about aligning portable gis with portableapps...
I'm curious about this command prompt issue. Can you explain in a little more detail, as it's not something I have come across, or that anyone else has mentioned. When exactly does this happen- when you run the setup programmes? What error do you get?
Other than when you try and use the FWTools command line tools, there should be no need to manually run a command prompt to use the packages, so this is all very curious.
I have heard elsewhere that pgadmin works well on a usb stick, so I'm going to include it in the next release in preference to phppgadmin. But, whenever I use pgadmin (on whatever machine), I always have to specify the maintenance database to set up a new connection anyhow- isn't this normal?
One other issue- do you see much benefit in setting things up as services? A lot of people have asked about including postgresql within xampp, portableapp or not- it will be interesting to see how you get on with that!
All the best
Jo
Alex Mandel
Nov 4, 2008, 4:36:20 AM11/4/08
to portab...@googlegroups.com
Jo,
Basically if I open a cmd on windows and try to cd to the usb drive it
doesn't take:
Example:(can't remember exactly all the things I tried)
C:\> cd E:\usbgis
Tells me it can't find the path. It may be specific to the labs I've
been in lately.
In particular I'm trying to use psql which is a command prompt tool that
you need to use if you want to copy in csv files to postgres, not just
shapefiles which the SPIT plugin should handle. In this particular case
I was connecting to both local and server based databases.
Yes pgadmin requires the maintenance database, but the default in all
the databases I've ever used is postgres not template1. I just tried
that and it happened to work but it was a guess and not pgadmin's
default for that box.
The nice part about the XAMP service manager is that you can tell what
is running and don't have cmd windows cluttering the desktop. It makes
it easy to start and stop the various pieces at will. ie, I only want
Postgres or I want postgres and Apache with phppgadmin and it's all on
one interface you toggle from the taskbar, not a menu with sub menus.]
http://www.apachefriends.org/en/xampp-windows.html#1173
I'll look into integrating with XAMP it shouldn't be too hard with the
bat scripts you've already provided. I see an add-ons link and will
follow that.
http://portableapps.com/apps/development/xampp
Alex
Jo Cook
Nov 4, 2008, 5:08:46 AM11/4/08
to portab...@googlegroups.com
Hi Alex,
I've just tried the standard windows command prompt on two random pcs at my work place and can't reproduce that problem, so that's an odd one. Does it work with other usb sticks?
Apologies for the lack of documentation about the postgresql side of things- basically if I had waited until I had been able to finish all that side of things it would still be an idea in my head!
I would be interested to know how the using xampp components as services works when you transfer sticks around and use it in a locked down environment. If it works fine, then I don't mind going over to that, but of course the provided xampp menu would need quite a lot of work to include all the other web-based components that are in portable gis.
You would still need the portable gis menu to include the setup programmes and the ability to run the other components of the setup- not just the xampp ones, so while it would be an improvement on that one aspect I still need to give people an easy way to setup and access grass, gvsig etc. So what you'd end up with is two menus in your system tray rather than one menu and some command prompts.
Postgresql/Xampp integration- do look beyond the portableapps version, because it only currently exists for linux in the full version of xampp, and there must be a lot of interest in it. It shouldn't be difficult to integrate, though I don't have the software to re-compile their exe files to add it in- so I'd have to make my own from the various batch files.
Cheers
Jo
I've just tried the standard windows command prompt on two random pcs at my work place and can't reproduce that problem, so that's an odd one. Does it work with other usb sticks?
Apologies for the lack of documentation about the postgresql side of things- basically if I had waited until I had been able to finish all that side of things it would still be an idea in my head!
I would be interested to know how the using xampp components as services works when you transfer sticks around and use it in a locked down environment. If it works fine, then I don't mind going over to that, but of course the provided xampp menu would need quite a lot of work to include all the other web-based components that are in portable gis.
You would still need the portable gis menu to include the setup programmes and the ability to run the other components of the setup- not just the xampp ones, so while it would be an improvement on that one aspect I still need to give people an easy way to setup and access grass, gvsig etc. So what you'd end up with is two menus in your system tray rather than one menu and some command prompts.
Postgresql/Xampp integration- do look beyond the portableapps version, because it only currently exists for linux in the full version of xampp, and there must be a lot of interest in it. It shouldn't be difficult to integrate, though I don't have the software to re-compile their exe files to add it in- so I'd have to make my own from the various batch files.
Cheers
Jo
pyroGIS
Nov 6, 2008, 11:54:44 AM11/6/08
to Portable GIS
Alex & Jo,
This was very helpful Alex - THANK YOU:
" " Just wanted to point out that I had a hard time getting Postgres
to
work and thought these tips would help others.
1. I had to install PortableApps: Portable Command Prompt - for some
reason the machine I was on didn't want to cd to the flash drive F:
2. In order to connect to the database via pgadmin III or psql I had
to specify the database as template1
Example:
psql -d template1 -U postgres " "
I also am experiencing the cmd prompt business .. I easily installed/
integrated the version from http://portableapps.com/apps/utilities/command_prompt_portable
and got that working.
Can either of you link to the other OSGEO posts regarding this
subject? (I'll do the standard searches to see if I can find 'em, but
it might be nice to link here for others.)
Alex, are you saying that pgAdmin integration is as easy as installing
to the Desktop and moving to USB?
I'm green to OS GIS in general - so still getting acquainted with
PgSQL overall. I'm actually now working on " " 3: To make all
the next step. I really appreciate the help friends! Especially clear
posts of code or commands for PgSQL. Looking forward to more
contributions here or OSGEO or wherever. ***a.k.a. j03lar50n
> > > On Mon, Nov 3, 2008 at 9:46 PM, Alex Mandel <tech_...@wildintellect.com
> Email: jo.k.c...@googlemail.com
This was very helpful Alex - THANK YOU:
" " Just wanted to point out that I had a hard time getting Postgres
to
work and thought these tips would help others.
1. I had to install PortableApps: Portable Command Prompt - for some
reason the machine I was on didn't want to cd to the flash drive F:
2. In order to connect to the database via pgadmin III or psql I had
to specify the database as template1
Example:
psql -d template1 -U postgres " "
integrated the version from http://portableapps.com/apps/utilities/command_prompt_portable
and got that working.
Can either of you link to the other OSGEO posts regarding this
subject? (I'll do the standard searches to see if I can find 'em, but
it might be nice to link here for others.)
Alex, are you saying that pgAdmin integration is as easy as installing
to the Desktop and moving to USB?
I'm green to OS GIS in general - so still getting acquainted with
PgSQL overall. I'm actually now working on " " 3: To make all
databases spatially enabled, you need to load the postgis templates
into template1, which contains the set of tables that get copied into
any new databases by default. " ", now that Alex's tip has moved me to
into template1, which contains the set of tables that get copied into
the next step. I really appreciate the help friends! Especially clear
posts of code or commands for PgSQL. Looking forward to more
contributions here or OSGEO or wherever. ***a.k.a. j03lar50n
> > >wrote:
>
> > >> It's seems to be a general problem on the machines I've tested that the
> > >> built in windows command prompt doesn't like switching to a drive letter
> > >> assigned to a usb device. Not really sure why, but portableapps Command
> > >> Prompt Portable works fine.
>
> > >> Interacting with the database is fine, there's just the oddity of having
> > >> to specify template1 as the maintanence database, this really only
> > >> applies to using PgAdmin (Which runs from a portable drive no problem)
> > >> and doesn't influence the connection in psql once one makes their own
> > >> database.
>
> > >> I'm am interested in trying to roll Postgres into the Portable apps
> > >> implementation of XAMMP, they have a nice gui for starting and stopping
> > >> services which could also be used for other server type apps too.
>
> > >> If I get around to it I'll try to work PgAdmin into something easy to
> > >> install also. Right now it's an install on a desktop dragged to the
> > >> Portable apps folder which works fine and it menu launchable.
>
> > >> I'll start another thread to discuss maintanence and upgrades of apps
> > >> and working with the OSGeo Live project.
>
> > >> Thanks,
> > >> Alex
>
>
> > >> It's seems to be a general problem on the machines I've tested that the
> > >> built in windows command prompt doesn't like switching to a drive letter
> > >> assigned to a usb device. Not really sure why, but portableapps Command
> > >> Prompt Portable works fine.
>
> > >> Interacting with the database is fine, there's just the oddity of having
> > >> to specify template1 as the maintanence database, this really only
> > >> applies to using PgAdmin (Which runs from a portable drive no problem)
> > >> and doesn't influence the connection in psql once one makes their own
> > >> database.
>
> > >> I'm am interested in trying to roll Postgres into the Portable apps
> > >> implementation of XAMMP, they have a nice gui for starting and stopping
> > >> services which could also be used for other server type apps too.
>
> > >> If I get around to it I'll try to work PgAdmin into something easy to
> > >> install also. Right now it's an install on a desktop dragged to the
> > >> Portable apps folder which works fine and it menu launchable.
>
> > >> I'll start another thread to discuss maintanence and upgrades of apps
> > >> and working with the OSGeo Live project.
>
> > >> Thanks,
> > >> Alex
>
pyroGIS
Nov 6, 2008, 4:02:39 PM11/6/08
to Portable GIS
Group,
Just thought I would share a couple links for anyone else out there
who's like me - a rather green n00b to PostgreSQL + PostGIS (OS GIS
overall, I was raised on ESRI) ..
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/ -
helped me install postgis on my ubuntu machine, shows basic commands
that may help
http://www.bostongis.com/ - has many resources .. Getting Started with
PostGIS, PostGIS 1.3.3 pgsql2shp shp2pgsql Command Line Cheatsheet,
and more
Good luck * joe
On Nov 6, 8:54 am, pyroGIS <pyro...@gmail.com> wrote:
> Alex & Jo,
>
> This was very helpful Alex - THANK YOU:
>
> " " Just wanted to point out that I had a hard time getting Postgres
> to
> work and thought these tips would help others.
> 1. I had to install PortableApps: Portable Command Prompt - for some
> reason the machine I was on didn't want to cd to the flash drive F:
> 2. In order to connect to the database via pgadmin III or psql I had
> to specify the database as template1
> Example:
> psql -d template1 -U postgres " "
>
> I also am experiencing the cmd prompt business .. I easily installed/
> integrated the version fromhttp://portableapps.com/apps/utilities/command_prompt_portable
> ...
>
> read more »
Just thought I would share a couple links for anyone else out there
who's like me - a rather green n00b to PostgreSQL + PostGIS (OS GIS
overall, I was raised on ESRI) ..
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/ -
helped me install postgis on my ubuntu machine, shows basic commands
that may help
http://www.bostongis.com/ - has many resources .. Getting Started with
PostGIS, PostGIS 1.3.3 pgsql2shp shp2pgsql Command Line Cheatsheet,
and more
Good luck * joe
On Nov 6, 8:54 am, pyroGIS <pyro...@gmail.com> wrote:
> Alex & Jo,
>
> This was very helpful Alex - THANK YOU:
>
> " " Just wanted to point out that I had a hard time getting Postgres
> to
> work and thought these tips would help others.
> 1. I had to install PortableApps: Portable Command Prompt - for some
> reason the machine I was on didn't want to cd to the flash drive F:
> 2. In order to connect to the database via pgadmin III or psql I had
> to specify the database as template1
> Example:
> psql -d template1 -U postgres " "
>
> I also am experiencing the cmd prompt business .. I easily installed/
>
> read more »
Alex Mandel
Nov 6, 2008, 10:46:50 PM11/6/08
to portab...@googlegroups.com
Yes, if you make a pgadmin folder on your drive and copy a desktop
install into it then refresh the portable apps menu you will see it on
the list and it will work.
install into it then refresh the portable apps menu you will see it on
the list and it will work.
Note and exe in that top folder will show up on the list, when I get
around to it I'll try to write a small wrapper to get around the listing
of all the exe files.
Ah yes if make a new database and want it to have postgis you need to
specify template1 as the template for it and that will copy all the
functions and create the 2 tables required.
Alex
Reply all
Reply to author
Forward
0 new messages