New answers tagged spatial
this way not ok in speed and Accuracy if you seperate x and y new fild better than. UPDATE myTable SET geoPoint_x = :x , geoPoint_y = :y where geoPoint_id = :id i use the :x and :y for example . if you need to split text , you must split in in web server or in frontend jquery and send just x,y to database
I'm going to go out on a bit of a limb here and guess what you are trying to achieve. I suspect that you want to find the nearest neighbour from within the DS1 table for all the rows in DS1. For a test set I created the following randomly filled table. -- Test table CREATE TABLE DS1 ( MSPID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY, Location ...
Specifying the spatial index using the WITH (INDEX = ) option works: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WITH (INDEX = [...]) INNER JOIN ... ON ... WHERE [Location].STDistance(@Location) IS NOT NULL AND @CurrentMSPID = [MSPID] ORDER BY [Location].STDistance(@Location);
A reach SELECT TOP 1 DS2.[Location].STDistance(@Location) FROM [DS1] DS1 INNER JOIN [DS2] DS2 ON DS1.[MSPID] = @CurrentMSPID and DS2.[MSPID] = @CurrentMSPID WHERE DS2.[Location].STDistance(@Location) IS NOT NULL ORDER BY DS2.[Location].STDistance(@Location); Do you really need the join? SELECT TOP 1 DS2.[Location].STDistance(@Location) FROM ...
1) The purpose is to provide a logical representation of a data entity (coordinates for example or a path) that can be indexed and queried in Postgresql. And also postgresql can understand this datatype. 2) Technically it's possible but you're better of storing it as two float so you can filter on them. It's never recommended to try to outsmart database ...
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 ...
Top 50 recent answers are included