Connecting to PostgreSQL/PostGIS from .NET

Another developer on the team had a need to project coordinates in decimal degrees to state plane on the fly (i.e. all we had was a pair of coordinates). As ArcIMS was totally dissatisfied with the idea, I suggested using a simple SQL command to PostgreSQL/PostGIS. Then came the next logical question - how does one connect to PostgreSQL from a .NET web service?

As it turns out, it was a lot easier than I expected. There’s a .NET driver for PostgreSQL at http://www.pgfoundry.org/projects/npgsql. Download the latest driver (currently Npgsql0.7.1-bin.zip), and copy the two dll’s in the Npgsql/bin/ms1.1 folder of the zip file to the bin folder of your .NET project. That’s it.

The .NET code is easily recognizable as standard (more or less) ADO.NET. First, import the Npgsql namespace.

Imports System
Imports System.Data
Imports Npgsql</
span>

Next, open a connection to PostgreSQL.

Dim conn As NpgsqlConnection = New NpgsqlConnection(“Server=your_server; Port=5432; User Id=your_user_name; Password=your_password; Database=your_database;”)
conn.Open()

Next, issue the command. It looks more complicated than it is; the PostGIS documentation has a lot of details about the various functions, but this one creates geometry from digital degrees, transforms it to state plane, and extracts the x and y coordinates. Note we’re not calling any tables - we’re just running a PostGIS function.

Dim command As NpgsqlCommand = New NpgsqlCommand(“select x(transform(GeomFromText(‘POINT(-80.759453 35.2485)’,4326),2264)) as xcoord, y(transform(GeomFromText(‘POINT(-80.759453 35.2485)’,4326),2264)) as ycoord”, conn)

Then we simply open up a datareader and toss out the state plane coordinates.


Dim dr As NpgsqlDataReader = command.ExecuteReader()
dr.Read()
response.write(dr.Item(“xcoord”) & “ “ & dr.Item(“ycoord”))

That’s all there is to it. It’s faster than a blink, and you can send all kinds of geospatial questions at it - buffers, intersections, overlays, etc.

The PC isn’t the world’s biggest .NET fan, but for things like web services it’s awfully convenient. By using native .NET drivers for PostgreSQL you can take advantage of both worlds. Enjoy!