Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

learn more… | top users | synonyms

6
votes
2answers
2k 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
2
votes
2answers
202 views

Problem with SPIT (error while importing layers to POSTGIS)

I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here: SPIT ...
3
votes
1answer
479 views

SQL Server data modelling + spatial data = what book? [closed]

What recommended book can you provide about data modelling for geographical spatial data (geometry) in SQL server?
4
votes
3answers
778 views

How to select a number of rows based on current sort order

I would like to be able to select a certain number of rows from my attribute table based on their ordering and not their attributes. For example, if I sort by ObjectID, I then want to select the top ...
3
votes
2answers
1k views

Get Spatial Table Information Oracle Spatial

How can I get the tablename, Srid and GeometryType of all spatial tables in Oracle(I use 11g)? In postgis, I can do something like select f_table_name,srid,type from public.geometry_columns order by ...
6
votes
3answers
845 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
2answers
1k 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 ...
6
votes
1answer
4k views

STIntersects - Update field to give me count of points in polygon

I have a points layer (dbo.ptLayer) Around 1M points Spatial Geometry Type (dbo.ptLayer.geom) No spatial index just yet, but will create one once data gathering complete. I have a polygon layer ...
4
votes
2answers
761 views

how to cast integer to float in an ogrinfo sql statement?

I have a shapefile for which I would like to add the values in a column. As the sum exceed the capacity of an integer, I need to cast values to a float. I tried the following: ogrinfo -sql "SELECT ...
0
votes
0answers
134 views

Can osm way records in SQL spatial be merged to one geographic record if they have same Id?

I've imported England shp file of roads to mssql using OSM2MSSQL and I've noticed that the same road has couple of records with same Id but different geographic (probably it was segmented). Is there ...
2
votes
0answers
1k views

How to handle postgresql/postgis geometry type errors in a function?

I have created the following function in PL/SQL to make a polygon from a set of linestrings: CREATE OR REPLACE FUNCTION _myfunction( _fid integer, _rid integer ) RETURNS VOID AS $$ ...
2
votes
1answer
438 views

How to return nearest point to input lat/long with distance?

I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the ...
6
votes
3answers
375 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 ...
3
votes
1answer
88 views

PostGIS - improving sql statement

I've managed to create the below statement that creates a table based on a point in polygon query. However it creates a column that gives a true/false value which I then delete out all the f values ...
2
votes
1answer
612 views

How do I select * rows of multiple columns in one query using Select by Attributes within ArcMap?

I am trying to convert a psql query string into arcpy formatting for an UpdateCursor where expression parameter: '''SELECT ...
4
votes
1answer
505 views

Is it possible to run an arcpy execute SQL method without connecting to any database or server?

I want to simply process a layer by writing a tool script in a toolbox without having to connect any database, so I think I may have to set up a workspace environment in ArcMap, but I'm not sure if I ...
7
votes
1answer
879 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 ...
1
vote
1answer
324 views

Should I query the tab/shp file or SQL (mapserver or geoserver)?

I have tab files (or shp) with information like residents , buildings etc... I need to be able to click a point in web map and query all data that fall in certain radius. I didn't decide yet which WMS ...
7
votes
2answers
12k 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 ...
3
votes
1answer
296 views

Editing and Deleting Query Tables in ArcMap

How do I edit the query for a Query Table after it has been created? How do I permanently delete a Query Table? Even after I do right-click -> remove, it still appears in dropdown lists.
4
votes
3answers
1k views

Incorporating SQL Statements in ModelBuilder?

I have a point data set where each point has a unique operator associated with it. I'd like to select by attribute = operator name and generate a new shapefile for each operator containing only the ...
3
votes
2answers
939 views

Parameterizing SQL Views Geoserver

I must do a view in geoserver. I seen some example here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html. The example show the operator like for parameterizing view. Suppose, now ...
7
votes
0answers
75 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
0answers
1k 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 ...
2
votes
1answer
457 views

Why is my PostGIS transformation not working?

I am using a PostGIS query to find the intersection of two streets, and then transform the results into lat/lon. The street database's current SRID is 6856, a projection I added to PostGIS myself; and ...
4
votes
1answer
1k views

raster2pgsql: Strange error when creating a table

Hi GIS Stackexchange people, When I try to load a asc file into my psql setup, I get a strange error message. I have created the sql input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F ...
2
votes
1answer
114 views

Selecting more than one feature from raster attribute table using ArcGIS Desktop?

How do I select more than one features from raster attribute table? For example: "lc_2001" = 21, 22, 23, 24 AND "lc_2006" = 11, 31, 41, 51
3
votes
1answer
373 views

How to query against time using ArcGIS Desktop?

I have a date-time field in a feature class which I want to query. The field has values like "2011-01-01 00:00:29". I want to query the data between the time 00:06:00 and 00:12:00. The date does not ...
1
vote
0answers
3k views

How to populate data from postgresql using dynamic php\html listing

In the form I created, I need two fields to be dynamically populated from the database while the other fields will be manually entered and push to the database. I have written a script that is able ...
2
votes
1answer
179 views

How to create a network with all the track points

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database. Now I have a table which holds all the track points of these ...
4
votes
1answer
2k views

Python script for constructing a WHERE clause from user input

I'm trying to construct a where clause from user input that will eventually be passed through as a SQL statement in the select tool or the select by attributes tool. I've started a script thats been ...
5
votes
3answers
20k views

Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10

I finally (albeit reluctantly) made the switch from a personal geodatabase (pGDB) to a file geodatabse (fGDB) not too long ago. The main reason it took so long to switch has to do with how Select by ...
6
votes
1answer
524 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 ...
1
vote
1answer
210 views

run sql on Qgis

I want to do simple demo to our manager with quantum gis. i have buildings from my city with wgs84 projection on postgre sql database and also have non gis table which have who leaves this buildings ...
2
votes
2answers
3k views

Formatting SQL queries for use in Arcpy?

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis ...
1
vote
0answers
74 views

Database has duplicate values after running pgrouting

I am a newbie with postgresql and sql statements so maybe it is a trivial question, I'm sorry for that. I am working on a postgis database and try to use the pgrouting function. My goal is to store ...
3
votes
1answer
153 views

Proximity query approach

Let's say that I have two tables in PostGIS, tracks and cities, each table has two columns, in both cases the columns are called name and way, name is a string, way is a geometry. I want to retrieve ...
2
votes
4answers
2k views

Finding center point of MULTILINESTRING using PostGIS?

How to find the center point of MULTILINESTRING, which lies on the multiline? I am using SQL in PostGIS
1
vote
2answers
3k views

How to offset point perpendicular to line direction in PostGIS

I have a postgis table of points, that are sitting on road centerlines (I've linear referenced them). And the second table with road centerlines themselves. The point table also has a field roadid and ...
4
votes
3answers
2k views

Publish SQL Views from Postgres/Postgis to Geoserver

I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver. Does ...
3
votes
3answers
2k views

How to Calculate length of polyline geometry for several tables in PostGIS?

PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request: SELECT ...
2
votes
1answer
157 views

Join Zip Codes Table to Nearest City from Second Table

I have two tables, siteareas and ziplookup. The former contains large cities in the US; the latter all US zips with lat/lngs. I have a foreign key into the former table in the latter: sitearea_id. I ...
2
votes
1answer
665 views

How to create a point table from a remote csv file?

The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following: -- create table CREATE TABLE points( id int, lat double precision, lng double ...
27
votes
4answers
15k 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 ...
3
votes
1answer
792 views

Duplicate ObjectID in ArcSDE Table

After making a check in from an arcpad AXF file, i notice that there's duplicate ObjectID records. This ArcSDE PostgreSQL table is versioned and edited by Axf checkins and a .net engine application. ...
46
votes
2answers
7k 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 ...
6
votes
3answers
8k views

Creating join based on multiple fields using ArcGIS Desktop?

I have two feature classes in a File Geodatabase that I would like to join based on multiple fields. I've searched this site and Google and all I have come up with was to use the Make Query Table ...
1
vote
0answers
107 views

How to find loops in a network

I am a new in this field and need help, how to code using SQL spatial a function that retrieve loops in a network.The input, flag: Geography and barrier: Geography and two spatial tables, first ...
0
votes
1answer
765 views

How to select multiple features using advanced search SQL in QGIS

I have a layer of planning units covering a large area (there are over 16000 PU's). Three of the columns in the attribute table hold information about which planning units were included in the ...
2
votes
1answer
356 views

Direct DBMS sql queries and ArcSDE geodatabase

i want to make updates from Arcobjects on several tables stored on a ArcSDE 10.1 versionned geodatabase based on Postgresql 9.0.5 , none of these update require spatial data , and making these updates ...