Archive for the ‘ Postgresql ’ Category

Batch Geocoding with TIGER Geocoder 2010

I’m compiling a database of all the microbreweries in my state.  So far I’ve got a list of 43 breweries and their addresses.  I’ve geocoded them against the 2010 TIGER street centerlines.  I used a batch geocoding example from PostGIS in Action the basis for this query.

Continue reading

Changing permission on files created by pl/r

With pl/r you have all the functions and abilities of R itself built available to you from your Postgres queries.  Among these is the ability to create image files of graphs.  When you create these graphs, however, by default they are given read-write permission to only the user who controls the Postgres daemon, usually that user is postgres.  There are no permissions for group or other so to see the image file you have to either chmod or chown the file as root and grant the permissions after the fact.  To save this step you can add the chown statement to your pl/r function.

system("chmod 666 /tmp/graph.png")

With the above statement your image, graph.png, will be read-writeable to the file’s user, group, and everyone.

plr

PL/R is one of many language extensions to Postgres that allow you to write functions and triggers in the programming language of your choice, in this case the R language.  R is widely used in statistical analysis, the GIS community is using it more and more often, see this Boston GIS pl/r tutorial for more information.

To get pl/r up and running on Arch you first have to have R itself installed.  Luckily R is in the main package repository and can be installed with a simple pacman -S r.  The R package puts its dynamically linked library in a non-standard location so you’ll need to tell the library linker where to find it so pl/r can make use of it.  To do so go to the /etc/ld.so.conf.d directory and as root, or through sudo, create a file called R.conf.  The file only needs one line, the path to the R library:

/usr/lib/R/lib

Then as root or through sudo run:

ldconfig

Next you’ll need to install pl/r from AUR.  If you’re running a 64 bit system you’ll want to edit the PGKBUILD file’s Arch section and add ‘x86_64’.  It is missing from the 8.3.0.13-1 PGKBUILD, it may be added to later versions.

Once pl/r is compiled and installed you’ll need to add it to the databases you want to do statistical analysis on.  If you’re running Postgresql 9.1 or greater pl/r can be installed as an extension.  The easiest way to do so is through pgadmin3.  Just select the database, right click on Extensions, select Add New, then select pl/r from the drop down.

Quick and easy way to create read-only databases

Postgresql doesn’t have a one-step command to grant permissions on a batch of tables like you can in MySQL.  However, it is much simpler in 9+ than it was previously.  You still have to go schema by schema in a database, but at least you don’t have to go table by table, view by view.   Here are the steps to create a read-only user:

First, create the user:

CREATE USER readonlyusername WITH PASSWORD 'password';

Now for each schema in a database you want the read only user to have access to you need the next two statements:

GRANT USAGE ON SCHEMA schema TO readonlyusername;

GRANT SELECT ON ALL TABLES IN SCHEMA schema TO readonlyusername;

If you’ll be adding new tables or views and you want them to be accessable by your read only user you need the following:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO readonlyuser;

If you know of a quicker and/or easier way please let me know!

TIGER Georeference 2010 Loader Works

A few posts ago I went through the steps to get the script that the 2009 TIGER Georeference Loader to work under Linux.  Regina Obe was nice enough to comment on that post, pointing out that the 2010 Loader that’s included in PostGIS 2.0 works just fine under 1.5.  It does work right out of the box and it downloads the 2010 TIGER data instead of the 2009.  Sweet!

One small tip, I used the Pgadmin3 query editor tool and copied the results of the Loader function to a blank text file and executed that text file as a script.  When doing so, remember to delete the first and last quotation marks you’ll get when you copy the query results.

Well, that was quick

Compile from source, problem solved.  Plus now I’ve got the shp2pgsql GUI to play with.  Huzzah!

I should have used the silent option when restoring the database dump.  2 GB worth of insert successful messages take forever to scroll by.

Upgraded Postgres, borked PostGIS

Oops.  Postgres 9.1 hit the Arch repositories a few weeks ago, and I finally decided to run the upgrade.  I dumped all my databases, ran the upgrade, and re-imported the dumps.  Now PostGIS is totally borked, the geography datatype isn’t recognized and most of the PostGIS functions aren’t in the database.  I tried re-installing PostGIS via Pacman, but that didn’t help.  Arch’s forums and wiki are both down, I’m going to wait until they pop back up to see if there’s something I missed there.  If not, I’ll try installing PostGIS from source.