Postgresapp: pgrouting with postgresApp

[dkalisch]

Does someone know how I can use / install pgrouting with postgresApp?
Thanks for your help!

[mattt]

I spent about an hour trying to compile pgrouting from source to include in Postgres.app, to no avail. Seriously, that project is a hot mess—absolutely refuses to link correctly on OSX, lacks any semblance of documentation on installation (to the point of assuming rather non-trivial dependencies like Boost).

If anyone is interested in taking time to get this to work—go for it. I'm washing my hands of this myself, though. For anyone who's brave/curious, here's where I ended up in creating a pgrouting aggregate build target:

Run Script: Download & Compile cmake

#!/bin/sh

mkdir -p ${PROJECT_DIR}/src
cd ${PROJECT_DIR}/src

/usr/bin/curl -L10 -O "http://www.cmake.org/files/v2.8/cmake-2.8.7.tar.gz"
/usr/bin/tar xzf cmake-2.8.7.tar.gz
cd cmake-2.8.7

sh ./bootstrap --prefix="${PROJECT_DIR}/Postgres/Vendor/postgres"
/usr/bin/make
/usr/bin/make install

Download & Build Boost

#!/bin/sh

mkdir -p ${PROJECT_DIR}/src
cd ${PROJECT_DIR}/src

/usr/bin/curl -L10 -O "http://downloads.sourceforge.net/project/boost/boost/1.51.0/boost_1_51_0.tar.gz"
/usr/bin/tar xzf boost_1_51_0.tar.gz
cd boost_1_51_0

sh ./bootstrap.sh --prefix="${PROJECT_DIR}/Postgres/Vendor/postgres"
./b2 --prefix="${PROJECT_DIR}/Postgres/Vendor/postgres" --without-python  

Download, Build, & Install pgrouting

#!/bin/sh

mkdir -p ${PROJECT_DIR}/src
cd ${PROJECT_DIR}/src

#/usr/bin/curl -L10 -O "http://download.osgeo.org/pgrouting/source/pgrouting-1.05.tar.gz"
#/usr/bin/tar xzf pgrouting-1.05.tar.gz
cd pgrouting-1.05

export PATH="${PROJECT_DIR}/Postgres/Vendor/postgres/bin/:${PATH}"

#${PROJECT_DIR}/Postgres/Vendor/postgres/bin/cmake" -DPOSTGRESQL_INCLUDE_DIR="${PROJECT_DIR}/Postgres/Vendor/postgres/include/server" -DBoost_DIR="${PROJECT_DIR}/src/boost_1_51_0/boost" .
"/usr/local/bin/cmake" -DPOSTGRESQL_INCLUDE_DIR="${PROJECT_DIR}/Postgres/Vendor/postgres/include/server" -DBoost_DIR="/usr/local/Cellar/boost/1.49.0" .

/usr/bin/make install

[dkalisch]

Thanks for your work, matt. It seems to be a mass, that's what I figured out as well... But I am not a programmer, so maybe someone can help with that...?

[kachkaev]

Has anyone figured out how to install pgRouting for Postgres.app yet?

[miccferr]

I second kachkaev comment. Any news will be greatly appreciated!

[frewie]

Would be great if that would work at some point.

[mtaptich]

Okay. I haven't fully tested its functionality, but I could possibly have a work around if your are getting 'undefined symbols while linking' errors.

This issue was resolved by changing the filepath in /pgrouting/build/CMakeCache.txt:

POSTGRESQL_EXECUTABLE:FILEPATH=/Applications/postgres.app/Contents/Versions/9.4/bin/postgres

I am also getting two warnings:

src/tsp/src/tsp2.c:57:5: warning: implicit declaration of function 'get_typlenbyvalalign' is invalid in C99
      [-Wimplicit-function-declaration]
    get_typlenbyvalalign(i_eltype, &i_typlen, &i_typbyval, &i_typalign);
    ^
src/tsp/src/tsplib.c:115:16: warning: implicit declaration of function 'Rand' is invalid in C99
      [-Wimplicit-function-declaration]
        last = Rand ();
               ^

Since I am trying to learn pgRouting, I will confirm the work around once I run my first test. Please, feel free to add to my post if you have more experience with this. Cheers!

[dkalisch]

An alternative is to use the grouting from http://www.kyngchaos.com/software/postgres (make sure that the Version of postgres matches the one grouting is build for!)
Then you only have to do the following 3 adjustments:

Before you install the pgrouting package set the path of the Postgres.app to the expected path. ln -s /Applications/Postgres.app/Contents/Versions/9.3/ /usr/local/pgsql-9.3

After installing, create a symbolic link to the installed grouting extension: ln -s /Applications/Postgres.app/Contents/Versions/9.3/share/extension/pgrouting* /Applications/Postgres.app/Contents/Versions/9.3/share/postgresql/extension

And last but not least, in /Applications/Postgres.app/Contents/Versions/9.3/share/extension/pgrouting--2.0.0.sqlchange $libdir/to /Applications/Postgres.app/Contents/Versions/9.3/lib/ (adjust 9.3 with the correct version!)

After that you can install posts and grouting with

CREATE EXTENSION postgis;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION pgrouting;

and verify that everything works fine:

SELECT postgis_full_version();
POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER


SELECT * FROM pgr_version();
 version |       tag       | build |  hash   | branch | boost  
---------+-----------------+-------+---------+--------+--------
 2.0.0   | pgrouting-2.0.0 | 0     | f26831f | master | 1.54.0
(1 row)

Happy routing :-)

[dassouki]

@dkalisch , I'm on osx 10.10.2 and although I followed your instructions, I'm having an issue: I can't find /Applications/Postgres.app/Contents/Versions/9.3/share/extension/pgrouting*, however, I can find pgrouting in /usr/local/pgsql-9.3/share/extension/ . Any suggestions or comments?

[dkalisch]

@dassouki: That means that you didn't set the symbolic link:

ln -s /Applications/Postgres.app/Contents/Versions/9.3/share/extension/pgrouting* /Applications/Postgres.app/Contents/Versions/9.3/share/postgresql/extension

[ctimmins]

The wrong (but works) way:

I have another installation of postgres

ll $(which postgres)
$: /usr/local/bin/postgres -> usr/local/Cellar/postgresql/9.5.2/bin/postgres

brew install pgrouting. Should be located in /usr/local/Cellar/pgrouting/2.1.0_1/share/postgresql/extension

if you start the postgress.app and run ps aux | grep postgres you can see the process running that looks like /Applications/Postgres.app/Contentents/yada/yada -D <some config directory>. Now kill it.

run postgres -D <some config directory from above>
now go into the psql shell and create extension pgrouting
should work and you should be all set to use it from postgres.app next time you run the server

[RP-3]

@ctimmins Lifesaver!
Might be useful for everyone else, make sure your postgres.app and alternative installation are of the same minor version.

Some commands that might be useful if juggling homebrew's postgres and postgres.app:
brew list services
brew switch <some other version of postgres>
brew search postgresql

[Fivelfivel]

@ctimmins
just confirming this worked for me too. thanks!!

[timkofu]

Would it be possible to include pgrouting in Postgres.app?

[tbussmann]

@timkofu: inclusion of pgrouting is currently tracked in #288 - the issue with that is a dependency that we did not yet figure out how we want to deal with. You can however fulfil that dependency by other means (e.g. homebrew) and build pgrouting yourself against PostgresApp. An (not fully up to date) set of instructions can be found here. I'll try to compose a list of contributed instructions on how to build various extension against PostgresApp and look into the boost issue for pgrouting / postgis_sfcgal when I find some spare time but I cannot promise when that will happen.

[timkofu]

Building it manually worked. Thank You.