PostgreSQL and SQL Server 2008 (DBI-Link)
At work, we have a few issues with spatial support for SQL Server 2008.
In SQL Server 2008, we have a large system in production that manages a bunch of important things. In some tables, I have coordinate pairs and need to be displayed in ArcGIS and other GIS software.
My question here is really: is it possible to use DBI-Link (PostgreSQL tool) to connect to SQL Server 2008?
What kind of performance loss should I expect? I don't expect complex queries to be executed. It's just a matter of reading from a PostgreSQL view inside SQL Server 2008 (simple view, for example SELECT * FROM foo
).
So what do you think about this? I know this is a bit of a hackish solution, but inside SQL Server I lose a lot of spatial processing functionality and all my databases in SQL Server stores are coordinate pairs.
Yes, this should work fine if you have the DBI driver installed correctly.
Performance - Depends on what you are doing. DBI-link does not have the ability to drop constraints, so if your view is in "SELECT * FROM foo" it will always do so. If your application is "SELECT * FROM myview WHERE pk = 1", it will still query the entire table with SELECT * and then filter it on the pg side. You might be better off using functions that can tailor the request.
As long as your queries aren't shuffling a lot of data, the performance is usually pretty decent.