[postgis-users] QGIS - PostGIS - ArcGIS Interaction

[Bborie Park]

Hey Doug,

ArcMap 10 brought "read-only" access to PostGIS without the need for 
ArcSDE or any other extensions.  To do so...

1. Click on File > Add Data > Add Query Layer...

2. In the "New Query Layer" window that appears, click on the 
"Connections..." button to add a new connection.  Unless you have 
existing connnections, click on "New..." in the "Manage Connection(s)" 
window.

3. In the "Database Connection" window, select "postgresql" for the DBMS 
client, provide your database server IP (or domain name) in "Data 
Source" and fill out "Database" with the name of the database you're 
connecting to.  If need be, add the appropriate authentication 
information.  For PostgreSQL, you'll select "Database Authentication" 
for Authentication.  Feel free to test your settings with "Test Connection".

4. Once satisfied, click "OK" to close the "Database Connection" window.

5. By now, you should have an entry in the "Manage Connection(s)" window 
so click "OK" if you're satisfied.

6. You should be back in "New Query Layer" and the connection you just 
created may have already been selected for "Connection".  If not, select 
the appropriate connection.  It takes a few seconds for the connection 
to be established and the program to retrieve the schema of the database.

7. I usually skip clicking on the "List of Tables" and "Columns" tools 
as ArcGIS still doesn't work with 64-bit datatypes (double, bigint, 
text???) and go directly to the "Name" and "Query" elements on the 
window.  Just provide a value for "Name" as that value will be the layer 
name and use any valid SQL SELECT statement in the "Query" field.

8. You can click on "Validate" to make sure your query is valid.  Once, 
satisfied, click on "Finish"

That's it.

-bborie

On 06/15/2011 11:53 AM, Doug_Newcomb at fws.gov wrote:
>
> bborie,
> I would like details on this procedure.  Does it require the
> interoperability extension? Ziggis?
>
> Doug
>
> Doug Newcomb
> USFWS
> Raleigh, NC
> 919-856-4520 ext. 14 doug_newcomb at fws.gov
> ---------------------------------------------------------------------------------------------------------
>
> The opinions I express are my own and are not representative of the
> official policy of the U.S.Fish and Wildlife Service or Dept. of the
> Interior.   Life is too short for undocumented, proprietary data formats.
>
> -----postgis-users-bounces at postgis.refractions.net wrote: -----
>
> To: postgis-users at postgis.refractions.net
> From: Bborie Park<bkpark at ucdavis.edu>
> Sent by: postgis-users-bounces at postgis.refractions.net
> Date: 06/15/2011 06:47PM
> Subject: Re: [postgis-users] QGIS - PostGIS - ArcGIS Interaction
>
> James,
>
> If you have ArcGIS 10, you can directly write SELECT queries from ArcMap
> to PostGIS.  That's how I visualize my data when I'm running Windows.
>
> -bborie
>
> On 06/15/2011 10:58 AM, James David Smith wrote:
>> Dear all,
>>
>> I appreciate that this is more of a QGIS query, but I think that the
>> route of the problem might be in PostGIS, so... I have opened QGIS and
>> ran the query on my PostGIS table as below:
>>
>> SELECT * FROM incidents
>> WHERE urgency = 'Immediate'
>> AND date_time_of_arrival - date_time_unit_assigned>    '00:12:00'
>>
>> This gives me 33 points in QGIS. Great. I would now however like to
>> save this as a ShapeFile for use in ArcGIS with which I am more
>> familiar, but when I try to SAVE AS from QGIS I am given the following
>> error....
>>
>> Export to vector file failed.
>> Error: trimming attribute name 'date_time_of_arrival' to ten
>> significant characters produces duplicate column name.
>>
>> I am guessing that the problem here is that ESRI shapefiles can only
>> deal with column names that are ten characters long...? And that as I
>> also have a column called 'date_time_of_departure', when QGIS trims
>> this column it ends up with two columns called the same thing?  Is
>> there a way around this? I realise I could rename the column in QGIS,
>> but I actually have around 45 columns in the table and this problem
>> will occur with about 15 pairs of them I think. I guess there is no
>> simple answer... but thought I'd ask.
>>
>> Cheers
>>
>> James
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkpark at ucdavis.edu
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu