Thread: distance vector databases and country maps

I am looking for info on free/open source (or if that's not available,
any other product) products which can be integrated with PostgreSQL that
provide geometric vector data on geographic maps.

In particular, I need that for Germany. What I want to do is to find
cities within a 50-100 km radius of any given location on the map. I
know that quite a few people are working on this and that such solutions
exist; I just don't know where to start looking. Also, my impression is
that all of these solutions are heavily copyrighted, patented etc. and
that it's probably really expensive to get to use this kind of data.

Can anyone help out with info, starting points?

Thanks, Frank

Hi,

I believe postgresql has polygon, box, circle etc datatypes which could be
useful in your situation. Depending on how you are doing your maps the
world being approximately spheroid may or may not be a consideration in how
you approach things (to avoid 2-D wraparound issues for instance).

Datatypes
http://www.postgresql.org/idocs/index.php?datatype-geometric.html#AEN3665

Operators/functions to use with those data types.
http://www.postgresql.org/idocs/index.php?functions-geometry.html

e.g.
Operator: @
Meaning: Contained or on
Usage: point '(1,1)' @ circle '((0,0),2)'

Indexes
http://www.postgresql.org/idocs/index.php?indices-types.html

CREATE INDEX name ON table USING RTREE (column);

I haven't any experience in using these features but I figure they might be
helpful to you.

Cheerio,
Link.

p.s. Yeah Postgresql is also copyrighted, but it's BSDtype :).

At 05:33 PM 10/6/01 +0200, you wrote:
>I am looking for info on free/open source (or if that's not available,
>any other product) products which can be integrated with PostgreSQL that
>provide geometric vector data on geographic maps.
>
>In particular, I need that for Germany. What I want to do is to find
>cities within a 50-100 km radius of any given location on the map. I
>know that quite a few people are working on this and that such solutions
>exist; I just don't know where to start looking. Also, my impression is
>that all of these solutions are heavily copyrighted, patented etc. and
>that it's probably really expensive to get to use this kind of data.
>
>Can anyone help out with info, starting points?
>
>Thanks, Frank
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>

Frank,
In terms of storing GIS (geographic informations systems) data in
PostgreSQL, check out PostGIS: http://postgis.refractions.net . It is a
LGPL extension to PostgreSQL to provide full spatial database
functionality for pgsql. The built-in types are just not up to full GIS
work.

As for your particular problem, yes, acquiring GIS data, particularly
detailed GIS data, can be expensive. It depends a great deal on the
country you are working in. Data here in Canada is terribly expensive;
in the USA it is much more reasonable (even free for some data sets). I
am sure there are government agencies in Germany which have very
detailed GIS data for your country, but I have no idea which ones :) I
have found the the GIS portal site http://www.geocomm.com is a good
starting point when looking for free data. Free data tends to be more
coarse than the alternatives but then at least it is free :)

Happy mapping,
Paul

> At 05:33 PM 10/6/01 +0200, Frank Jeordens wrote:
> >I am looking for info on free/open source (or if that's not available,
> >any other product) products which can be integrated with PostgreSQL that
> >provide geometric vector data on geographic maps.
> >
> >In particular, I need that for Germany. What I want to do is to find
> >cities within a 50-100 km radius of any given location on the map. I
> >know that quite a few people are working on this and that such solutions
> >exist; I just don't know where to start looking. Also, my impression is
> >that all of these solutions are heavily copyrighted, patented etc. and
> >that it's probably really expensive to get to use this kind of data.
> >
> >Can anyone help out with info, starting points?
> >
> >Thanks, Frank

Hi,

Is it possible to have multiple postgresql installations on one machine
running on the same port but on different IP addresses?

I've specified different IPs/hostnames for the virtual_host but the unix
socket clashes when I try.

Should I just move the unix sockets somewhere else with -k, what would the
impact be? I can't seem to tell psql where to find the moved unix socket.

Cheerio,
Link.

Charles Tassell <ctassell@isn.net> writes:
> I may be wrong, but I don't think it's possible to have them on the same
> port.  The problem is that the TCP/IP port defaults to the same as the UNIX
> sockets port, and UNIX sockets are local to the machine, so they are
> completely independent of IP addresses and can not be duplicated.

Unix sockets need to have unique pathnames in the local filesystem.
The PG "port number" is actually only a component of the file name.
So all you need to do if you need to have conflicting port numbers
is to vary the unix_socket_directory parameter for each postmaster.

However, varying the port number is probably a simpler attack...

>> I can't seem to tell psql where to find the moved unix socket.

Use -h /path/to/directory/containing/socket.  AFAIR you can set the
path in PGHOST environment variable, if that helps.

            regards, tom lane

Thanks. I didn't know psql -h did that, thought it was for hostnames only :).

So I resorted to TCP access for the rest of the installations and moved the
sockets out of the way. However the Redhat style initscript tends to
shutdown all installations - even though I specified a different PID file
:(. I guess I might need to fix that one - coz the idea is to have them
independent.

Was actually wondering if I could change the name of the socket to
something like ...portnumber.virtual_host_name_or_ip_address

That way psql -h vhost1 would try usualprefix.defaultport.vhost1

psql by itself would try usualprefix.defaultport

(have to convert dots and slashes in addresses to underscores or something
more innocuous, or quote carefully)

Probably not that useful now that I know more about -h :).

Cheerio,
Link.

At 12:03 AM 10/13/01 -0400, Tom Lane wrote:
>Charles Tassell <ctassell@isn.net> writes:
>> I may be wrong, but I don't think it's possible to have them on the same
>> port.  The problem is that the TCP/IP port defaults to the same as the
UNIX
>> sockets port, and UNIX sockets are local to the machine, so they are
>> completely independent of IP addresses and can not be duplicated.
>
>Unix sockets need to have unique pathnames in the local filesystem.
>The PG "port number" is actually only a component of the file name.
>So all you need to do if you need to have conflicting port numbers
>is to vary the unix_socket_directory parameter for each postmaster.
>
>However, varying the port number is probably a simpler attack...
>
>>> I can't seem to tell psql where to find the moved unix socket.
>
>Use -h /path/to/directory/containing/socket.  AFAIR you can set the
>path in PGHOST environment variable, if that helps.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>