Return to Answer

added 315 characters in body

Source Link

edited Oct 12, 2015 at 20:16

I have done this before with out too much trouble using ArcGIS 10. I1 and 10.2 unfortunately it does not work with 9.3 and postgres 9.2 I think from memory.

I used the drivers from esri. Logon to the ESRI customer care site although I think this has changed since I wrote the instructions.

TipQueryLayer4.png

The only thing to watch for is that the data being returned has to have a unique field that can be used as a primary key by ArcGIS. Sometimes you may need to specify it if you are using more than just a basic query and ArcGIS cannot work out which field to use. You can do this by:

You can also run the spatial queries against the postgres data base with relative ease although you have to create amanufacture an id field on the fly.

I did a full tutorial e.g. Here is an example of doing a while back at: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands100km buffer.

SELECT row_number() over(order by cities.the_geom)::integer as oid,
ST_BUFFER(cities.the_geom, 100000) AS the_geom,
cities.name
FROM mygis.public.cities As cities

Not only that you can also save any query layer as a layer file and pass it through some of the standard ArcGIS tools as well. I have not tested this too much. So adding columns and stuff like that I could see that causing havoc. I think you could make spatial SQL call like thatcalls with other databases like SQLServer and Oracle as well with a bit of fiddling to create the on the fly id numberfield.

I did a full tutorial a while back at: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands

I have done this before with out too much trouble. I used the drivers from esri. Logon to the ESRI customer care site although I think this has changed since I wrote the instructions.

TipQueryLayer4.png

You can also run the spatial queries against the postgres data base with relative ease although you have to create a id field on the fly.

I did a full tutorial a while back at: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands

Not only that you can also save any query layer as a layer file and pass it through some of the standard ArcGIS tools as well. I have not tested this too much. So adding columns and stuff like that I could see that causing havoc. I think you could make spatial SQL call like that with other databases like SQLServer and Oracle as well with a bit of fiddling to create the on the fly id number.

I have done this before with out too much trouble using ArcGIS 10.1 and 10.2 unfortunately it does not work with 9.3 and postgres 9.2 I think from memory.

I used the drivers from esri. Logon to the ESRI customer care site although I think this has changed since I wrote the instructions.

You can also run the spatial queries against the postgres data base with relative ease although you have to manufacture an id field on the fly. e.g. Here is an example of doing a 100km buffer.

SELECT row_number() over(order by cities.the_geom)::integer as oid,
ST_BUFFER(cities.the_geom, 100000) AS the_geom,
cities.name
FROM mygis.public.cities As cities

Not only that you can also save any query layer as a layer file and pass it through some of the standard ArcGIS tools as well. I have not tested this too much. So adding columns and stuff like that I could see that causing havoc. I think you could make spatial SQL calls with other databases like SQLServer and Oracle as well with a bit of fiddling to create the on the fly id field.

I did a full tutorial a while back at: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands

Source Link

answered Oct 12, 2015 at 20:01

andy3092

I have done this before with out too much trouble. I used the drivers from esri. Logon to the ESRI customer care site although I think this has changed since I wrote the instructions.

Select your version
"Software Downloads"
"DMBS Support Files"
Scroll down until you see "PostgreSQLQL Client Libraries (Windows)", should be 2.21 MB in size.
Click Downloads

For PostgreSQL/PostGIS in the downloaded file should be the "pg_client_windows86" client set of libraries containing the necessary 32 bit version of libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll. Copy these to your ArcGIS bin directory. On my computer using 10.1 it was: C:Program Files (x86)ArcGISDesktop10.1bin If you are using 32bit windows it would be something like: C:Program FilesArcGISDesktop10.1bin

Once you have done this you should be able to connect to and add data from your database. To use a data query layer you will first need to connect to your database. In ArcGIS 10.1 you will need to go to File>Add Data>Add Query Layer

TipQueryLayer4.png

First validate the SQL query by clicking on the Validate button (ArcGIS needs to generate a list of fields that are in the select statement to populate the advanced options dialog box).
Once the SQL had been validated then click on Show advanced options the finish button will change from Finish to Next>
Click on Next> and you can then access the advanced options dialog.
In the advanced options dialog you are given a list of all the fields for the table. Select one field that will act as a unique identifier field - the default is to have all the fields selected.
You can also set the spatial reference system of your data if it is appropriate to do so.
Click finish and then your layer will be added to your map.

You can also run the spatial queries against the postgres data base with relative ease although you have to create a id field on the fly.

I did a full tutorial a while back at: http://www.gisuser.org.nz/resources/tips-and-tricks/look-mum-dad-no-hands

Not only that you can also save any query layer as a layer file and pass it through some of the standard ArcGIS tools as well. I have not tested this too much. So adding columns and stuff like that I could see that causing havoc. I think you could make spatial SQL call like that with other databases like SQLServer and Oracle as well with a bit of fiddling to create the on the fly id number.