Hot answers tagged

25

I could not immediately reproduce this on 2014 - 12.0.4213.0 but do see it on SQL Server 2016 (CTP3.0) - 13.0.700.242. On the 2014 build (with no DBCC errors) the plan looks as follows. And on the 2016 build (with DBCC errors reported) like this. The second plan has a single row coming out of the merge anti semi join, the first plan zero rows. The join ...


21

I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to ...


16

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY data type. Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this: INSERT INTO t1 (pt_col) ...


13

Constructor Syntax Looking up the spatial function reference, you'll see Point(x, y) Constructs a Point using its coordinates This isn't entirely correct. All GIS implementations must do (x,y) for projected coordinates which is (long,lat). But, on geodidic cordinate systems there is some disagreement about what to do. MySQL (and SQL Server) do (lat,long) ...


12

Let me caveat that I am playing with spatial data in SQL server for the first time (so you probably already know this first part), but it took me a while to figure out that SQL Server isn't treating (x y z) coordinates as true 3D values, it is treating them as (latitude longitude) with an optional "elevation" value, Z, which is ignored by validation and ...


10

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available. By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by ...


9

The simplest solution without PostGIS would be to store lat/long as two number columns. numeric for exact precision. double precision or even just real if you don't need the precision. I see no reason why the data type point shouldn't work as well. Per documentation: Points are the fundamental two-dimensional building block for geometric types. Values ...


9

You may consider using a GIST index based on using the function ll_to_earth. This index will allow for fast "nearby" searches. CREATE INDEX ON locs USING gist (ll_to_earth(lat, lng)); Once you have this index, your query should be done in a different way. Your (lat, lng) pairs need to be converted to the earth type, and compared with the indexed ...


9

The reason you are having a problem is that the polygon is wound the wrong way which results in a polygon that covers the entire world except for your area of interest. Try DECLARE @t_point geography; DECLARE @t_polygon geography; set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 0, 5 5, 1 1))', 4326); set @t_point = geography::Point(0, 0, 4326)...


8

R-tree structure works in a way that two nearby points are "closer" in the R-tree index - because both coordinates and both with same weight are used to decide where (in the index) a new point is to be placed. So, it's easy to identify points that are "near" a fixed point - meaning points that have both coordinates near the fixed point coordinates. B-tree ...


8

How up-do-date do your read queries need to be? You could partition the database by time if the map just needs to show the most recent measurement. This would reduce your query load for the map. For the history of a given point, you could hold a second store by x and y showing the history. This could be done with a nightly refresh/update as the the ...


8

Try ShortestLineTo(): SELECT pt1.ToString(), pt2.ToString(), pt1.ShortestLineTo(pt2).ToString() AS line -- what goes here FROM #tmp; Returns POINT (1 1) POINT (2 2) LINESTRING (1 1, 2 2) POINT (2 2) POINT (5 5) LINESTRING (2 2, 5 5) POINT (3 3) POINT (4 4) LINESTRING (3 3, 4 4) POINT (4 4) POINT (3 3) LINESTRING (4 4, 3 3) POINT (5 ...


7

MongoDB has built in support for geoindexing. You don't need to do the calculation yourself. Basically, you would create a field with the lat/long stored as an array or as sub documents, something like one of these: { loc : [ 50 , 30 ] } //SUGGESTED OPTION { loc : { x : 50 , y : 30 } } { loc : { lon : 40.739037, lat: 73.992964 } } Then index the new ...


7

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item. Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run ...


7

Sounds like you want to get rowgoals to play their part on the query - so try using TOP(1), maybe with testing to avoid NULLs (in case of non-matching SRIDs). That way you can get the "nearest neighbour" functionality to kick in. I know you're using Contains, but you want to use a method that tells the QO that you're only going to get a single row back. ...


7

I know a bit about Oracle performance and pretty much nothing about custom data types, but I'll try to give you a plan to improve performance. 1) Verify that you cannot get an explain plan. It's possible to get explain plans even if you don't have sophisicated database software. What happens if you execute set autotrace on explain? You could also try ...


7

From Indexing and Querying Spatial Data in Oracle in the Oracle® Spatial Developer's Guide 11g Release 2 (11.2): Querying Spatial Data Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins. The term two-tier indicates that two distinct operations are performed to resolve queries. ...


6

Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time. You can achieve the same by doing the update in a single statement: UPDATE list_of_location SET location = ST_MakePoint(...


6

PostGIS supports it with ST_GeoHash ST_GeoHash — Return a GeoHash representation (geohash.org) of the geometry.


6

Correct. Spatial indexes don't get leveraged in that situation, sadly. Spatial indexes provide a set of grids, allow the system to identify geometries (or geographies) that overlap these grids. Your best bet is to set a threshold of acceptable closeness, and try that, using something like STBuffer. STIntersects works well, and you can increase this ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


6

You could use MySQL's spatial extensions with GIS. In a code example from Google displaying points on a map, they state: When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. To keep ...


6

Short answer: Compare actual execution plans for the fast and slow variants and you'll see yourself. When the given @point is close to the points in the table, the tessellations used in the spatial index actually help to dismiss most of the rows and only few seeks of the index are necessary. When the given @point is far from any point in the table, the ...


6

The relational model and the object-oriented paradigm The state-of-the-art relational model, originated in 1970 by Dr. E. F. Codd, is applied science concerning the field of database administration. Its two solid pilars are first-order logic and set theory. The object-oriented paradigm, as devised by Dr. Alan Kay, is an approach useful to build application ...


6

Assuming current Postgres 9.6, it can work. Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual. Related answer with more details: Postgres 9.5 foreign table inheritance ...


6

Your syntax for ALTER INDEX...REBUILD WITH (DATA_COMPRESSION...) is correct. The problem is actually revealed in the error message: Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement. For a spatial index in SQL Server 2008, using the data_compression option is not supported. According to Microsoft Documentation, support ...


5

Well you could use IBM DB2 LUW (Linux,Unix, Windows) Express-C edition. It is free and has community support. It is the same engine/binaries as DB2 Enterprise Edition, it just has certain features "turned off" and has memory and CPU caps, but for what you are describing, it may suit your needs. If you do find you need more memory/CPU you could always ...


5

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list. use simply vendor.location.Lat or location.Lat or define a table alias and use that. CREATE TABLE dbo.Vendor([location] geography) GO /*Works fine*/ CREATE VIEW V1 AS SELECT v.location.Lat AS latitude FROM dbo.vendor v GO /*Fails*/ CREATE VIEW V2 AS SELECT ...


5

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself. Consider this simplified form: CREATE FUNCTION store_distance(_lat double precision ...


5

Regardless of any other improvements you make, be sure to test the impact on spatial execution times of enabling trace flags 6532, 6533, and 6534 (start-up only). These turn on native code spatial implementations. SQL Server 2012 Service Pack 3 or SQL Server 2014 Service Pack 2 required (Microsoft Support article). Native compilation is on by default from ...


Only top voted, non community-wiki answers of a minimum length are eligible