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.
source to share
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.
source to share