Oct 9, 2008 at 7:35 PM — Steve
Hi Leland,
I’m up and configuring Geoserver via the web. Can’t change my password on login to the windows machine, I think because I don’t know its real domain name… . Ahh, it’s nice to be playing with a version of the server when I know I probably will have to do this just once… .
Oct 10, 2008 at 7:25 PM — Leland
LOL – I was keeping my eye on Facebook last night and did not see this message from you. sorry I missed it.
Your screen shot was helpful. When changing the password make sure that the username field is only smather, not www.byte….\smather. If that does not work, try PGSQL\smather. It looks like the MAC rdp client is trying to use ddddbsdn as the computername witch won’t work. “Macintosh!” (in my best “Newman!” voice).
Attached is an example of how the password change screen looks from the windows rdp client. Note that “log on to” option says “PGSQL”; that is the Windows computername. If you can choose this option as PGSQL, you should just use smather as the user name. If, however you can’t choose a log on to option, then use the PGSQL\smather for the username.
Here is what i have installed on this box so far so far…
Started Here – http://geoserver.org/display/GEOSDOC/1.1.1+Windows+Install
- JDK 6u7
- GeoServer (default admin pw)
Next followed these instructions http://pginstaller.projects.postgresql.org/ using all the defaults
- PostGreSQL
- PostGreSQL Service (windows) username:pguser pass:******* (begins with ***)
- Inital DB
- superuser: postgres pass:******
- Ran StackBuilder and installed PostGIS 1.3.3 for PostGreSQL
That is about the point I started falling asleep. 
I am putting the kid to bed in about 20 min. But I’ll be back later if you are interested in connecting.
later
Oct 10, 2008 at 9:04 PM — Leland
I have reviewed, but have not done anything on this yet. http://geoserver.org/display/GEOSDOC/PostGIS+DataStore ….
“how to avoid the problem of the data being occluded by the treetops themselves.” – Cut down all the trees and re-scan.
Tue, Oct 21, 2008 at 6:55 PM — Steve
Hmm, so would it be possible to open up the postgre port? Then I could use psql and PGAdmin natively on my Mac for configuring.
FYI, I’ve started playing with the database, just basic stuff, like adding a new database, dropping it, etc. Boy it’s been a while, but the tutorials are really easy to follow. PGAdmin is great ’cause it exposes options for which I have no understanding, but points me in the direction of things I still have to learn.
Oct 21, 2008 at 8:25 PM — Leland
Sure thing. Try it now.
I forwarded 5432. It appears from the pgAdmin III server properties that this is the port it is using. Let me know if there are any others that need opened up.
Oct 25, 2008 at 10:46 AM — Steve
I tried the connection on 5432, but my PGAdmin client reports that there is no host listening on that report. So I checked the postgresql.conf file and modified the host list to include my IP. We’ll see if this works… . I also added auto database vacuuming, ’cause I read somewhere that’s a good practise. I edited using file:open postgresql.conf in PGAdmin on the host machine.
Steve
Sat, Oct 25, 2008 at 10:48 AM — Steve
Although it looks like its easier to edit from tools:server configuration.
Unfortunately my changes haven’t helped… .
Sat, Oct 25, 2008 at 11:04 AM — Leland
The address you added (your ip) will not work here. I believe this setting tells pgsql which local network card to listen on. It can’t do that with your IP. Is there a pgsql setting for which ip addrs to accept connections from? If so, that is where to put ur ip addr. Especially if that setting is set to localhost.
I am out on some errands right now when I get home I can take a look at it.
Oct 25, 2008 at 11:52 AM — Steve
Hmm, I’ll have to look at that then. So much to understand yet. Looks like the setting for which hosts can connect is in the pg_hba.conf — I’ll let you handle that– I’m not really sure what to put for the subnet mask (if anything). Not all of the network tutorial has sunk in yet 
Here’s what I ran locally as a set of command line tests for creating and spatially enabling a new database, g4wd (although now that I’ve read more, it seems that there’s already a template PostGIS database that is configured as part of the Windows install, so I probably didn’t need to do anything I did below… ).
createdb g4wd
createlang plpgsql g4wd (unnecessary step, and postgre told me so)
psql -d g4wd -f lwpostgis.sql
psql-d g4wd -f spatial_ref_sys.sql
The next few I have recorded at the command line, after setting my number of lines of buffer retained in the cmd window:
psql g4wd
\d
\d geometry_columns
\d spatial_ref_sys
select postgis_full_version();
which actually looked like this:
C:\Program Files\PostgreSQL\8.3\share\contrib>”C:\Program Files\PostgreSQL\8.3\b
in\psql” g4wd
Password:
Welcome to psql 8.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
g4wd=# \d
List of relations
Schema | Name | Type | Owner
——–+——————+——-+——–
public | geometry_columns | table | pguser
public | spatial_ref_sys | table | pguser
(2 rows)
g4wd=# \d geometry_columns
Table “public.geometry_columns”
Column | Type | Modifiers
——————-+————————+———–
f_table_catalog | character varying(256) | not null
f_table_schema | character varying(256) | not null
f_table_name | character varying(256) | not null
f_geometry_column | character varying(256) | not null
coord_dimension | integer | not null
srid | integer | not null
type | character varying(30) | not null
Indexes:
“geometry_columns_pk” PRIMARY KEY, btree (f_table_catalog, f_table_schema, f
_table_name, f_geometry_column)
g4wd=# \d spatial_ref_sys
Table “public.spatial_ref_sys”
Column | Type | Modifiers
———–+————————-+———–
srid | integer | not null
auth_name | character varying(256) |
auth_srid | integer |
srtext | character varying(2048) |
proj4text | character varying(2048) |
Indexes:
“spatial_ref_sys_pkey” PRIMARY KEY, btree (srid)
g4wd=# select postgis_full_version
g4wd-# ();
postgis_full_version
——————————————————————————–
–
POSTGIS=”1.3.3″ GEOS=”3.0.0-CAPI-1.4.1″ PROJ=”Rel. 4.6.0, 21 Dec 2007″ USE_STAT
S
(1 row)
g4wd=#
Oct 25, 2008 at 12:07 PM — Leland
Hmmmm. I got home and tried a port scan on my router from an external machine. Did not find port 5432 on my router. From the host machine, I tried scanning 5432 on pgsql, and it did not find any open port there either. When I did a port scan locally (to localhost) on the pgsql box, It shows that 5432 is open. So these results show me that PGSQL is indeed using port 5432, but is is only allowing connections from the local machine. Any ideas?
Oct 25, 2008 at 12:10 PM — Steve
Now to check that we have one of the projections that we use in the GIS lab, Ohio State Plane North, NAD83, Meters available to us in PostGIS, I did a query on spatial_ref_sys looking for the code for Ohio State Plane (which I got from searching on the spatial reference web site):
g4wd=# select * from spatial_ref_sys where srid = 32122;
-[ RECORD 1 ]——————————————————————-
——————————————————————————–
——————————————————————————–
——————————————————————————–
——————————————————————————–
——————————————————————————–
——————————————————————————–
——————————————————————————–
————————
srid | 32122
auth_name | EPSG
auth_srid | 32122
srtext | PROJCS["NAD83 / Ohio North",GEOGCS["NAD83",DATUM["North_American_Dat
um_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUT
HORITY["EPSG","6269"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degr
ee",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4269"]],PROJ
ECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["standard_parallel_1",41.7],PARA
METER["standard_parallel_2",40.43333333333333],PARAMETER["latitude_of_origin",39
.66666666666666],PARAMETER["central_meridian",-82.5],PARAMETER["false_easting",6
00000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AU
THORITY["EPSG","32122"]]
proj4text | +proj=lcc +lat_1=41.7 +lat_2=40.43333333333333 +lat_0=39.66666666666
666 +lon_0=-82.5 +x_0=600000 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs
But we don’t have the equivalent projection in feet (as opposed to meters:
g4wd=# select * from spatial_ref_sys where srid = 102722;
(No rows)
So I attempted an insert statement to add it:
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 9102722, ‘spatialreference.org‘, 102722, ‘+proj=lcc +lat_1=40.43333333333333 +lat_2=41.7 +lat_0=39.66666666666666 +lon_0=-82.5 +x_0=600000.0000000001 +y_0=0 +ellps=GRS80 +datum=NAD83 +to_meter=0.3048006096012192 +no_defs ‘, ‘PROJCS["NAD_1983_StatePlane_Ohio_North_FIPS_3401_Feet",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",1968500],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",-82.5],PARAMETER["Standard_Parallel_1",40.43333333333333],PARAMETER["Standard_Parallel_2",41.7],PARAMETER["Latitude_Of_Origin",39.66666666666666],UNIT["Foot_US",0.30480060960121924],AUTHORITY["EPSG","102722"]]’);
But to no avail:
g4wd=# select * from spatial_ref_sys where srid = 102722;
(No rows)
Hmm. Need to do more research.
Sat, Oct 25, 2008 at 12:13 PM — Steve
Hmm, I don’t know. pg_hba.conf (available under Tools:Server Configuration in PGAdmin) appears to handle the external connections, but I don’t know if that is the whole story or not.
Oct 25, 2008 at 12:15 PM — Steve
I guess hba is host based authentication:
http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html
Sat, Oct 25, 2008 at 4:34 PM — Leland
Give it a try now. If your address is still 76.160.84.30, this should work but I’m not sure. I tried to add a similar entry for my network 10.0.0.xxx, but I still can’t seem to find 5432 on pgsql remotely with telnet or port scan, I need to get pgAdmin installed to test with, but I suspect it is not working yet. Try admin from your and and let me know your results.
I have finished all of my errands, unfortunately now I have to start my chores. (I can’t wait till I get down to just tasks.).
Oct 25, 2008 at 5:26 PM — Leland
Ok. We’re in business.
I found that I had to add the remote address to the pg_hba.conf file, and I had to add the local NIC address to the listen_addresses properties in postgresql.conf. After doing this and restarting the server, I was able to telnet and scan on 5432 remotely. Should work with pgAdmin too.
It pays to read the WHOLE conf file instructions…
#
# If you want to allow non-local connections, you need to add more
# “host” records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#
Oct 25, 2008 at 6:22 PM — Steve
I’m in!
Oct 25, 2008 at 6:25 PM — Leland
Got pgAdminIII running on Vista on my laptop. My first attempt to connect returned the error. missing or erroneous pg_hba.conf file When I checked the pg_hba.conf file on the server, I found that it had an erroneous host entry. en I fixed it, the connection worked!
Very cool that the client would not connect remotely due to and error in the remote connect config file.
Oct 25, 2008 at 7:04 PM — Leland
Cool! Too bad it took us all day. He He.
You should be able to stay connected until your IP address changes from its current value. We could put a larger range in to represent your ISP, (
This range: 76.160.84.30/32 only = 76.160.84.30/32
whereas This range: 76.160.84.30/16 = 76.160.*.*
)
but that would also mean that anyone on your ISP would be able to connect too. Not a big issue, unless they know the username and password, but they would already be twice as far in the door as user who’s ip is not on the list. Or, if we are a little more daring, we could script something.
Oct 25, 2008 at 7:27 PM — Steve
I’ve got cURL on my machine. It seems conceivable that I could write a bash script to periodically check my IP address and use a http post or get request to send that info to bs (actually an easy prospect).
Now, what the script would look like on the bs side? Maybe a targeted PHP script to consume the get request, and modify the postgre config file. Or maybe you have something more secure in mind.
Oct 25, 2008 at 7:57 PM — Steve
I’ve added the projection in question (state plane north feet). The sql command I was using was right, I just failed to understand what it was coding the spatial reference id (srid) as. I though it was coding it as 102722, but it is 9102722, so it just looked like it didn’t work.
So now, I should be able to load Cleveland Metroparks data. But first, I have to turn the laptop back over to April… .
Oct 25, 2008 at 8:01 PM — Leland
What you suggested is just what I had in mind. The only extra step would be to restart pgsql after the pg_hba.conf was updated. We should be able to restart the whole pgsql service via a script, but I don’t know if a full service restart is required. From the pg_hba.conf file…
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect. You can use
# “pg_ctl reload” to do that.
I have been restarting the service via pgadmin, but you may be able to re-load the conf using the “pg_ctl reload” command. Is that a pg server side command?
Oct 25, 2008 at 9:53 PM — Leland
random thoughts…
While thinking about this same task for treypod, it occurred to me that the scripting from end to end would be easier if I did not have to script finding my IP address first on the client side. Instead, I was thinking that if I could trust that a specially crafted HTTP get or put request was coming from my client, then I could trust the source IP of that request and use it to update the server’s copy of the clients IP.
The only problem with this is that an ISP operator can see the http request and replicate the URL from an undesirable IP address. Since the server is basing its trust on the specially crafted URL, once it is copied, it can’t be trusted. Even if the unscrupulous operator saw the URL, was clueless at to what it did and tried it, it would still automatically update the server with his IP address.
Oct 26, 2008 at 9:43 AM — Steve
What about https? Then at least we require and encryted handshake. I can script from the client side (in theory) FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP, TELNET, DICT, LDAP and LDAPS (although several of those I don’t know what they are).
Oct 26, 2008 at 9:48 AM — Steve
pg_ctl, from what I understand is a better way to stop and start than postmaster, since it gives more options. If used with the reload option it just forces pg to reread the configuration files, but without stopping or starting the server.
http://www.postgresql.org/docs/8.3/static/app-pg-ctl.html
Oct 26, 2008 at 9:50 AM — Steve
My guess is that pg_ctl reload just applies configuration changes to all new threads started on pg, which works for our purposes.
