Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
22
votes
9answers
9k views
How can I connect to a PostGIS database from ArcMap 9.3 and 10.0?
How can I connect to a PostGIS database from ArcMap 9.3 or 10.0?
Ideally, I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-spatial ...
17
votes
2answers
1k views
Acquiring ArcGIS-like speed in Postgis
I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case.
I tend to do heavy ...
13
votes
1answer
597 views
Identifying topological relationships using PostGIS
I'm trying to perform a basic typology analysis using PostGIS. My objective is to find any polygons that touch other polygons. To do this, I thought the ST_GetFaceEdges would work
(reference). I want ...
12
votes
5answers
3k views
Use arcgis with SQL Server 2008 as a backend
I'm wondering how best to use arcgis] and sql 2008 together. At the moment we run manual imports and exports between sql 2008 and arcgis 10 shape files. With sql 2008 having a geography data type I ...
12
votes
4answers
1k views
PostGIS: Select features that do NOT intersect
This seems to me like such a simple question (and it probably is) yet I cannot seem to find an example that gives me the answer. Using PostGIS, I just want to select points that fall outside of ...
11
votes
1answer
420 views
Nearest Neighbor Grouped Selection in QGIS
OK - so I have an interesting problem I am trying to solve. I have a list containing over 100,000 points in lat/long format which I have imported into qgis.
Now, what I am trying to do here is group ...
10
votes
3answers
873 views
How to interpolate GPS Positions in PostGIS
I have a PostGIS table of GPS positions for every five seconds:
2011-01-01 00:00:05, POINT(x1,y1)
2011-01-01 00:00:10, POINT(x2,y2)
2011-01-01 00:00:15, POINT(x3,y3)
...
I'm looking for a query ...
9
votes
4answers
1k views
What is the best way to join lots of small polygons to form a larger polygon?
I have the following layer using SRID 27700 in postgis:
It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
9
votes
2answers
253 views
How do I perform a 3 parameter geotransformation and projection in Sql Server 2012?
I have a table with latitude longitude (NAD27) columns in it. I compute two other columns, X and Y, representing Web Mercator (WGS84) location.
Currently I'm using a Arcmap to do this, by applying ...
8
votes
4answers
942 views
Queries returning very big datasets in PostGIS
I have a PostGIS query that will return several million rows:
SELECT
t1.id AS id1,
t2.id AS id2,
ABS(t1.mean_h - t2.mean_h) AS h_diff,
ST_Distance(t1.the_geom, t2.the_geom) AS dist
FROM ...
8
votes
4answers
1k views
To what extent is SQL's language accessible via ArcGIS tools?
That question just sounds so redundant...
At any rate, I have been asked to explore the uses of SQL in ArcGIS. We use an SDE DB for all server-side storage and DB managing/large-scale editing. ...
8
votes
5answers
2k views
A SQL console in QGIS
I know QGIS comes with its field calculator, which is not bad. It also comes with ftools which permits several operations on geometry and attributes. But we often need more control and I haven't seen ...
8
votes
2answers
562 views
Splitting polylines using regions with PostGIS
I was wondering if it is possible to split a polyline on polygon boundaries (regions) in PostGIS, the picture below is of what we have at the moment and showing where I need the polylines split:
...
8
votes
3answers
1k views
Beginner PostGIS question: getting geometry from multiple tables
I am very new to PostGIS, PostgreSQL, and SQL in general.
I have 44 tables in my PostGIS database, and each one represents a different layer of vector data. Each was loaded from a separate shapefile, ...
8
votes
3answers
655 views
Are there any training resources in SQL and Python as applied to GIS database management?
I've taken ESRI database courses, which have been helpful to my work. But I also need training in SQL and Python as applied to GIS database management. Any suggestions?
7
votes
1answer
590 views
PostGIS Intersection and Summarise Attributes
I am usually an ArcGIS desktop user, but keen to start using PostGIS more and I have a really big bit of processing to do. Not sure what functions to use, hopefully someone can help.
I have a polygon ...
7
votes
1answer
93 views
How to rectify the walls of buildings in PostGIS?
I need an operations in PostGIS to eliminate intermediate vertexes in a polygon to rectify the walls of a building.
I was revising http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesSpikeRemover but ...
7
votes
2answers
654 views
Select by Attributes based on SQL wildcard range
I want to select all the records from a geodatabase table where the string starts with a letter, so I tried
SELECT *
FROM tbl_names
WHERE "name" LIKE '[A-Z]%'
This returned no records. After some ...
7
votes
2answers
904 views
Intersecting a raster with a polygon using PostGIS - artefact error
I'm using PostGIS2.0 to do some raster/polygon intersections. I'm having difficulty understanding which operation I should use, and what the quickest way of performing this is. My problem is as ...
7
votes
1answer
281 views
Get all Nodes from a List of adjacent LineStrings in PostGIS
I use PostGis and pgRouting.
I have a table with geometries of the adjacent edges returned by a shortest path query. I want to concatenate these edges and retrieve a list of all nodes these edges ...
7
votes
1answer
326 views
Is it possible to define a No SQL model for spatial data? [duplicate]
Possible Duplicate:
Is there any way I can use a Key-Value store for geospatial data?
I read somewhere about No SQL being a solution for large databases management, the kind that google ...
7
votes
0answers
64 views
How can I automatically split a spatial layer to a grid [duplicate]
Possible Duplicate:
Divide vector in equal smaller parts in QGIS or similar
I am a software engineer and the company I work for has a project that needs some spatial data manipulation in ...
6
votes
1answer
794 views
Performance Gain Through GIST Index for Point in Polygon Query
I have two tables: locations(id,region_id,the_geom) and regions(id,the_geom). For each location point I want to determine the region it is located in:
UPDATE locations SET region_id =
(SELECT id ...
6
votes
1answer
552 views
Spatial joins in OGR SQL
I have seen that OGR supports it's own form of SQL, I was wondering if it's possible to do spatial joins.
Something like select all objects where one intersects the other. I did have a look around ...
6
votes
2answers
2k views
Creating spatial tables with PostGIS
In the PostGIS documentation it says that there are two steps to creating a spatial table with SQL:
Create a normal non-spatial table.
Add a spatial column to the table using the OpenGIS ...
6
votes
1answer
707 views
PostGIS and invalid SRIDs
I'm having difficulty with a PostGIS query. I am trying to query my database (world_countries) using a bounding box drawn on a google web map. The objective of this query is to find any countries that ...
6
votes
2answers
2k views
PostGIS: Find points that lie within a set of coordinates
I have a PostGIS db, and I want to find the points that lie within some region (a bounding box). I have two sets of coordinates, from which I can get all four rectangle points that form the box (if ...
6
votes
4answers
606 views
SQL expression for finding strings including letters in ArcGIS 9.3.1
I'm working on ArcGIS 9.3.1. I have a shapefile with a string field containing point names like these:
10
12
12/13
t/9
6577y
1234
12t9
124y67
u9
and so on (and yes, I know it looks like a colossal ...
6
votes
3answers
133 views
Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?
I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
6
votes
2answers
700 views
Join intersecting lines with PostGIS
This is something that I'm almost ashamed to ask but I can't seem to get it to work for the life of me.
I have a road layer with segments, each segment has a Road ID and a segment type.
I would like ...
6
votes
1answer
433 views
Editing tables using SQL in ArcMap
We've recently started working with ArcMap, having used MapInfo previously. I've created a shapefile in ArcCatalog and want to update some columns using SQL. In MapInfo I'd write the following command ...
6
votes
2answers
2k views
SELECT TOP query in ArcGIS
Is there a way to use a SELECT TOP (n) within ArcGIS desktop? Is appears that since the ArcGIS query builder is effectively building the SQL statement after the WHERE clause, that this may not be ...
6
votes
2answers
282 views
PostGIS SQL query to find a polygon that intersects with a line
I need to build a PostGIS query that would select every polygon inside an area the river is flowing through.
I'd better describe with an example below: There is a river (blue line) flowing through ...
6
votes
1answer
239 views
Assign Z value to start & end of PostGIS line
I have a line table in my PostGIS database and a table with invert levels for the start and end of each line.
My goal is to create create 3D lines in my table, from my invert data. I know I can use ...
6
votes
0answers
58 views
How to trace SQL queries sent by ArcGIS Server (ArcSDE) to Oracle database?
I'd like to generate a log file containing all SQL queries sent by ArcGIS Server (ArcSDE) to Oracle database.
Is there a way to do so?
I'm using Oracle 11g and ArcGIS Server 10.0 on Windows. ArcSDE is ...
6
votes
0answers
219 views
Getting ST_Difference between two different tables in postgis [closed]
One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
5
votes
5answers
2k views
How can one execute an SQL script in field calculator in ArcGIS 10?
I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use ...
5
votes
2answers
684 views
Using an integer variable in where-clause in arcgis python script
I've seen some questions on this, but there seem to be many different answers... and none have seemed to work. I'm trying to include an integer variable in the SQL where-clause of a tool like this ...
5
votes
1answer
5k views
Select by Attributes query question
Greetings, Nice looking alternative to esri forums here at gis.stackexchange.
I am trying to select multiple (50 or more) unique numeric records using ArcGIS 10's "Select by Attributes" using ...
5
votes
3answers
2k views
ST_Intersection slow query
I am trying to perform an intersection between two layers:
Polyline layer representing some roads (~5500 rows)
Polygon layer representing irregularly shaped buffers around various points of interest ...
5
votes
2answers
231 views
ArcSDE recently modified geometries
ArcSDE 10 with a MS SQL 2005 backend.
I am trying to see the most recently modified geometries using SQL so that I can use it to update another GIS database running PostGIS. Has anyone tried this ...
5
votes
1answer
275 views
How to filter points based on proximity while inserting into PostGIS?
I have a large amount of locations that need to be inserted to a PostGIS database. However, I'd like to aggregate these points so that if there's already a location, say nearer than a kilometer, then ...
5
votes
1answer
269 views
Postgres/postgis cursors
I have a road network with StreetName and min/max address ranges for each segment (ie- StreetName = 'Main St', Min = 100, Max = 199). I also have individual address points with similar information ...
5
votes
1answer
185 views
Clarification of Logical Operators in a PostGIS Database
The following query returns this result:
select count(*) from a , b where a.geom && b.geom;
count
-------
66514
On a postgis 2 database.
What does the logical operator AND ...
5
votes
2answers
201 views
Does SQL Server need extensions to do spatial?
Does SQL Server need extensions to do spatial like Oracle does?
Just wondering if there will be extra costs for implementing a SQL Server backend to SDE.
Thanks
5
votes
1answer
151 views
Join based on maximum overlap in PostGIS/PostGresQL?
I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
5
votes
2answers
321 views
Selecting POIs around specific buildings using PostGIS
The problem: I need to select, for each residential building in my table that has say at least 2 pharmacies and 2 education centers within a radius of 1km, all POIs (pharmacies, comercial centres, ...
5
votes
1answer
158 views
How to Comment Out Definition Queries
I use Definition Queries a lot in my daily GIS life, but one of the many things I've found annoying about ArcMap is the inability to comment out Definition Queries. I'd like to have this option as ...
5
votes
1answer
185 views
How to run custom queries on vector layers and display results?
Is there a way to display the result of a query in QGIS?
For instance :
To get the total number of inhabitants :
SELECT SUM( inhabitants ) FROM countries
To get the total density :
SELECT ...
5
votes
1answer
522 views
How to export a PostGIS raster to PNG?
I have a raster which is split into 30 tiles and I want to dump them all into PNGs.
I've read the documentation http://postgis.refractions.net/documentation/manual-svn/RT_ST_AsPNG.html but still ...
