I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like PID, SurveyID, GeoPoint Basically this is a summarize table where PID and SurveyID makes primary key for table. We do a survey on Animals and this summarize table record which Animal is surveyed at given Geo location on map. My Index was on column GeoPoint and PID. This table store around 400K record which is summarise from main table that has 2M record for faster execution. Now, we are planning to upgrade to MySQL 8.0 and we found that it doesn't support Spatial column been index with non-spatial column, so our index was not getting created. This results in our Query now take 8 second instead of 0.6 seconds from older version of MySQL. More over when I create index only on Spatial Column geopoint, ST_Contains, and MBRContains do not use that Index. Basically our query is as simple as we allow user to draw a Square on Google map and then we use those coordinate to find all Animals in that region. I am not sure how to solve this issue, as no documentation is found by me for it that help tweaking the query. Query: SELECT PID, count(distinct SurveyID) as totalsurvey FROM locationsummary where st_contains(ST_Envelope(ST_GeomFromText( 'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')), geopoint ) group by PID Table: CREATE TABLE locationsummary ( PID bigint(20) NOT NULL, SurveyID bigint(20) NOT NULL, Sitelat float NOT NULL, sitelong float NOT NULL, geopoint point NOT NULL, PRIMARY KEY (PID,SurveyID), SPATIAL KEY idx_geopoint (geopoint) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT Edit: 12th July 2019 Today I try to rerun queries and it is now executing in 2 seconds, still not good though, but some how it become better from last run.

I need to store a taxi route into MongoDB, however I'm not sure what is the best way to do this. Since the route is only needed for the taxi order, the first idea I had is to store it in the order, this would make the retrieval quite fast, since I won't need to join the order collection with the route collection. As well as that, the route can be stored in a simplified way for example:

{
   order_id: 1,
   ...
   route: [
        [ 1567767312, 58.542454, 110.15454, 45 ], //timestamp, lat, long, speed
        ...
        [ 1567768312, 59.448488, 10.484684, 20 ]

   ]
}

If say the measurement is done every 2 seconds, an average 15 min ride would be 450 points, which is not a lot. However, I read that MongoDB does not like when documents are very different in sizes and since the route can be different all the time, this might cause issues as the order number grows. The other obvious approach was to have a separate collection to store each reading a separate document:

{
    timestamp: "2019-09-06T10:49:38+00:00",
    coordinates: [ 58.45464, 128.45465 ],
    order_number: 1
}

With indexing, this should not be much slower in terms of fetching or writing than the method above. However, this will occupy way more space and the collection might grow really fast. Just as clarifications. The route is only used for displaying it on the map when somebody opens the order, no need for geo queries.

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a map. I am using HTML 5 mapping plus Google maps. I have converted the addresses I have, into long/lat and they are stored in a CSV file along with other attributes. I have tried storing as point, character varying and number as well as trying to create my own definition but have had no success. The table already exists and I just need to add these additional columns. I would rather not use PostGIS since that feels like adding in an additional level of complexity which I could well do without. So what do you suggest?

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658)) ) ---------------- select line_id, sdo_util.to_wktgeometry(shape) as well_known_text from a_sdo_geometry_tbl; LINE_ID WELL_KNOWN_TEXT -------------------------------------------------------------------------------- 1 LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613) 2 LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559) 3 LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675) 4 LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266) 4 rows selected. -------------------------- For each line, I want to select each vertex as a separate row in a query/resultset. I want to do this via a **recursive WITH clause** -- without using the GetVertices() function or a custom TYPE. Is there a way to do that? ---------------------- The resultset would look like this: LINE_ID VERTEX_ID X Y ---------- ---------- ---------- ---------- 1 1 671539.685 4863324.18 1 2 671595.050 4863343.17 1 3 671614.014 4863350.34 1 4 671622.204 4863353.53 2 1 71534.5567 4863119.99 2 2 671640.738 4863157.13 2 3 671684.862 4863172.02 3 1 671622.204 4863353.53 3 2 671633.327 4863357.85 3 3 671904.061 4863451.29 4 1 671684.862 4863172.02 4 2 671892.150 4863244.14 4 3 671951.216 4863264.82 4 4 671957.447 4863266.85 4 5 671966.824 4863269.15 Hints: The following functions might be useful: PointN: Returns a point that is the nth vertex in the collection of vertices GetNumVertices: Returns the number of vertices in the input geometry. ----------------

Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a way to accomplish this within SSMS?

Summary: I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to be able to iterate over these points using SQL and to access paths as arrays (or something functionally equivalent). --- Background: I'm a novice database user who is working with an unsupported legacy application which is using PostgreSQL 8.1 as a back-end database. I would like to be able to manually run some sanity checks on the data in the database in pure SQL (or PL/pgSQL), i.e. I'd like to be able to avoid using an external programming language, which is what I'm currently doing. Several tables in the database include columns containing geometric data. Here is a description of one such table: psql -U "${appuser}" -d "${appdatabase}" -c '\d+ path_table' Table "public.path_table" Column | Type | Modifiers | Description --------+---------+-----------+------------- id | integer | not null | path | path | not null | Indexes: "path_table_pkey" PRIMARY KEY, btree (id) Check constraints: "path_table_id_check" CHECK (id > 0) Has OIDs: no I consulted the section on [Geometric Functions and Operators](http://www.postgresql.org/docs/8.1/static/functions-geometry.html) from the [PostgreSQL 8.1 Manual](http://www.postgresql.org/docs/8.1/static/) , but to no avail. Since this might be relevant, I'll mention that I believe that PostGIS 1.3.6 is installed, i.e.: $ psql -U "${appuser}" -d "${appdatabase}" -c 'SELECT PostGIS_full_version();' postgis_full_version ---------------------------------------------------------------------------------------- POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS (1 row) My understanding is that I can get a list of all installed functions by using the psql \df meta-command, i.e. $ psql -U "${appuser}" -d "${appdatabase}" -c '\df' My understanding is also that all of the available PostGIS commands should be prefixed by the string 'ST_' (denoting spatial type), so that the following should produce a list of all the available PostGIS commands: $ psql -U "${appuser}" -d "${appdatabase}" -Alt -c '\df' | cut -d'|' -f2 | grep -i '^st_' Inspecting this list didn't produce any likely candidates besides the ST_Dump function, which doesn't appear to apply to a path type: SELECT st_dump(path_column) FROM table; ERROR: function st_dump(path) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. Apparently the st_dump argument needs to be of type geometry, so I tried following the hint and casting the path to a geometry type: SELECT st_dump(path_column::geometry) FROM table; ERROR: cannot cast type path to geometry I also consulted the [Geometry Accessors](http://postgis.net/docs/manual-1.3/ch06.html#id438545) of the [PostGIS 1.3.6 Manual](http://postgis.net/docs/manual-1.3) but apparently I don't know enough to be able to cull a solution from the documentation.

I have a MySQL table with linestrings:

CREATE TABLE edges (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  geometry linestring NOT NULL,
  type_key varchar(255) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at datetime DEFAULT NULL,
  PRIMARY KEY (id),
  KEY type_key (type_key, deleted_at),
  SPATIAL KEY geometry (geometry)
) ENGINE=InnoDB AUTO_INCREMENT=130966 DEFAULT CHARSET=utf8mb4;

I want to plot a subset of them on a map. To do so, I run the following query (the ? are variables):

SELECT * 
FROM edges 
WHERE deleted_at IS NULL 
  AND type_key = ? 
  AND MBRIntersects(geometry, LineString(Point(?, ?), Point(?, ?)))

The issue I'm facing, is that MySQL does not allow for multi-column indices that include spatial columns (so the geometry column). It therefore has to choose: either use the type_key index, or use the spatial geometry index. Which one is more efficient depends on the type_key chosen and the minimum bounding rectangle of the linestring constructed in the query. And in quite some cases, it's not efficient at all to only be able to use one of them: with about 100k rows in this table, it might very well take MySQL more than 500ms to compute that one such query results in 0 rows. I googled quite a bit on this topic, but could not find any good solutions. Interesting results were: * partitioning (see eg. http://mysql.rjweb.org/doc.php/latlng) , but the number of partitions you should make is quite limited (< 100, I read) * not using a spatial column at all: the geometry can be represented by a bounding box with four points. I could add all four as separate columns and add them to the index. That will help on one of them (as they are all inequality constraints), but will still be quite inefficient (because of the other 3) * migrating to other database software, eg. Postgres. I read that Postgres is able to use multiple indices in the same query (https://devcenter.heroku.com/articles/postgresql-indexes) , but it's not clear to me how efficient that is. I'm not clear on the alternative: whether a spatial column and other columns can be combined in a single index. I spent some time in setting up a test environment with the same data, but gave up after an hour or so, as it took me too much time. My question therefore is: how are others solving this issue?

Mongo lets any field type be used to set ranges for Shard Zones, but doesn't specify how analyses are conducted. My goal is to point queries and writes at the exact right server, WITHOUT using a compound shard key (for various execution complexities). I want to embed a unique user alpha-numeric ID, plus a latitude and longitude value into a single string, and use this as my shard key. Ex: (ID.lat.lon) "4kjaj29.48.-89" Would fall within the range: "0000000.45.-100" to "zzzzzzz.50.-80" Mongo uses utf8 strings, https://www.utf8-chartable.de/unicode-utf8-table.pl , in which for alpha-numeric characters 0 is the "least" and z is the "greatest". So I have to imagine this is how range inclusion is computed? Does anyone know off-hand if this approach is correct? And if not, can you point me in the right direction to accomplish this? There is definitely a way to accomplish this, since some kind of range inclusions calculation is occurring with string types, but just requires knowing the analysis strategy.

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/pp-adjacent.html According to the tutorial, in order to find the pairs of populated places that their distance is pp2.id AND pp2.ROWID IN ( SELECT pkid FROM idx_populated_places_geometry WHERE pkid MATCH RTreeDistWithin( ST_X(pp1.geometry), ST_Y(pp1.geometry), 0.02)) ORDER BY 2; Which throws an error because of no longer use of geocallback functions RTree***. I checked the update on this and this has been substituted by the use of virtual spatial index. We have to use a subquery of the form SELECT ROWID FROM SpatialIndex WHERE f_table_name = AND search_frame = So i tested this SELECT pp1.name AS "PopulatedPlace #1", GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)", pp2.name AS "PopulatedPlace #2" FROM populated_places AS pp1, populated_places AS pp2 WHERE GeodesicLength( MakeLine(pp1.geometry, pp2.geometry)) pp2.id AND pp2.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name ='populated_places' AND search_frame = pp1.geometry) ORDER BY 2; And the result set was awfully wrong PopulatedPlace #1 Distance (meters) PopulatedPlace #2 Ariano 0.000000 Ariano Campolongo Maggiore 0.000000 Campolongo Maggiore Campolongo Maggiore 0.000000 Campolongo Maggiore Campolongo Maggiore 0.000000 Campolongo Maggiore Campolongo Maggiore 0.000000 Campolongo Maggiore Campolongo Maggiore 0.000000 Campolongo Maggiore Campolongo Maggiore 0.000000 Campolongo Maggiore Ariano 0.000000 Ariano Could please someone explain this behavior and/or give an example of proper use of the virtual spatial index in order to obtain distance queries?

I have a set of of polygons. Sometimes they are completely disconnected (like separate patches). Other times, two sides of two polygons may touch each other (when the polygons are adjacent like on a chess board). The polygons never cross each other. In other words, their intersection is always empty. I need to check if a point is contained in any one of those polygons. Is there a way I can build a single geometry using these polygons and check if the point is in it? Currently I'm building a long WHERE close with OR conditions and do something like the below: st_contains(st_GeomFromText('POLYGON(("+polygon+"))'), st_GeomFromText(CONCAT('POINT()')))

I want to create a **fast refresh** materialized view (18c) on a **remote table**. The MV would also have a GEOMETRY column. Options for the GEOMETRY column datatype include: - ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype) - Oracle's SDO_GEOMETRY datatype ---------------- To start, I can successfully create a fast refresh MV ***without*** a GEOMETRY column: create materialized view log on maximo.workorder with primary key; --remote table grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema create materialized view my_gis_schema.wo_mv build immediate refresh fast start with sysdate next sysdate + (15/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, status, --other fields longitudex, latitudey from maximo.workorder@my_dblink ---------- The MV above works, but I want to store the XY coordinates from the remote table in a GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column). Unfortunately, my options for the GEOMETRY column in an MV seem pretty limited: 1. Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here ). - The SQL would be: sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape 2. Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the **fast refresh** option on a **remote table**: ORA-12015: cannot create a fast refresh materialized view from a complex query - The SQL would be: sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape --------------------- **Question:** Is there a way to include a GEOMETRY column in a materialized view on a **remote table**, using the **fast refresh** option?

I can't seem to get SQL Server to leverage a basic Spatial index on a GEOGRAPHY object for even the simplest of queries. Here is a [dbfiddle.uk repro](https://dbfiddle.uk/u_rQnpSq) which demonstrates creating a simple table with a GEOGRAPHY column called Coordinates, adding 1 row with a Polygon to that table, and then creating a Spatial index on the Coordinates column. The query appears like it should be SARGable, but I still get a clustered index scan, instead of a nonclustered index seek on the Spatial index: If I try to force the index hint, I get the classic error: >Msg 8635, Level 16, State 4, Line 15 > >The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparator supplied in the predicate. Try removing the index hints or removing SET FORCEPLAN. So it's not that SQL Server thinks the clustered index scan is more performant, rather it just can't even come up with a query plan for the Spatial index. According to [Microsoft's docs on Spatial Indexes](https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-ver16#geography) , the STDistance() function should be applicable in a predicate for Spatial index sargability: > Spatial indexes support the following predicate forms: > > - geography1.STDistance(geography2) <= number Am I being dumb?

I need help to improve query performance of a spatial query. I have the following query:

DECLARE @Longitude float,@Latitude float
SET @Longitude = -79.46278668733325
SET @Latitude = 43.70410072435495

SELECT
  GeoLocationBoundary.MakeValid().STCentroid().STDistance(
    geometry::STPointFromText(
      'POINT(' + CAST(@Longitude AS VARCHAR(20)) + ' ' + CAST(@Latitude AS VARCHAR(20)) + ')', 4326
    )
  ) as DistanceFromCentroid,
  *
FROM App.GeoFence
WHERE GeoLocationBoundary.MakeValid().STIntersects(
  geometry::STPointFromText(
    'POINT(' + CAST(@Longitude AS VARCHAR(20)) + ' ' + CAST(@Latitude AS VARCHAR(20)) + ')', 4326
  )
) = 1

What Index should I create as the one I created does not seems to be used when running the query.

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ST_Intersect. However I think I explained my own question while trying to formulate my question. I submit it anyway in case it would be useful to somebody. The manual for && says (ironically the page name is geometry_overlaps.html): > && — Returns TRUE if A's 2D bounding box intersects B's 2D boundingbox.` The manual for ST_Intersects says: > Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect) ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient. Here is an example which is direct copy from &&, ST_Intersects adaptation: SELECT t1.id AS t1, t2.id AS t2, t1.ln && t2.ln AS "&&", ST_Intersects(t1.ln,t2.ln) FROM ( VALUES (1, 'LINESTRING(0 0, 3 3)'::geometry), (2, 'LINESTRING(0 1, 0 5)'::geometry) ) AS t1(id,ln) CROSS JOIN (VALUES (3, 'LINESTRING(1 2, 4 6)'::geometry) ) AS t2(id,ln); t1 | t2 | && | st_intersects ----+----+----+--------------- 1 | 3 | t | f 2 | 3 | f | f (2 rows) Below is a simple graph of how these lines should look like. I just added plotted this to see how bounding box works exactly for lines. For my purposes, the points are always inside the box (while this line example does not really reflect what I am trying to do but good for illustration) Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box. Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

The SQL Server is pretty fast finding neighbors of a geography in a spatial index. Either it is finding nearest neighbors or all neighbors in specific area, the query result is instant. I am not able to get the result so fast, if I need to find neighbors for multiple points. I guess the business case is widely recognized. Let's say we have one table with coordinates of locations (it can be stores, restaurants, offices, etc.) and one table with coordinates of the users and their travel distance. In the context of the user, if I am in the center of the city and want to travel 5 miles - getting all locations in my range is fast. But in the context of a business, if I want to send email to all users who are in range of my stores, the query is pretty slow. Here is some sample date with several queries trying to find all users - locations pairs. DROP TABLE IF EXISTS #Users; DROP TABLE IF EXISTS #Locations; CREATE TABLE #Users ( [UserID] BIGINT pRIMARY KEY ,[Latitude] DECIMAL(9, 6) ,[Longitude] DECIMAL(9, 6) -- this is our user point and user's travel distances in meters ,[CenterGeography] GEOGRAPHY ,[TravelDistance] INT -- this is the [CenterGeography] * [TravelDistance] ,[AreaGeography] GEOGRAPHY ); CREATE SPATIAL INDEX [IX_#Users_CenterGeography] ON #Users ( [CenterGeography] ); CREATE SPATIAL INDEX [IX_#Users_AreaGeography] ON #Users ( [AreaGeography] ); CREATE TABLE #Locations ( [LocationID] BIGINT pRIMARY KEY ,[CenterGeography] GEOGRAPHY ); CREATE SPATIAL INDEX [IX_#Locations_AreaGeography] ON #Locations ( [CenterGeography] ); INSERT INTO #Users ([UserID], [Latitude], [Longitude], [TravelDistance]) SELECT CenterLocations.[CenterID] * 10000 + U.[value] ,CenterLocations.[Lat] + ABS(CHECKSUM(NEWID())) % 1000 * 0.0001 ,CenterLocations.[Long] + ABS(CHECKSUM(NEWID())) % 1000 * 0.0001 ,1 + ABS(CHECKSUM(NEWID())) % 5 * 1609 FROM GENERATE_SERIES(1, 1000) U CROSS APPLY ( VALUES (1, 13.404954, 52.520008) ,(2, 13.737262, 51.050407) ,(3, 6.783333, 51.233334) ,(4, 9.183333, 48.783333) ,(5, 6.953101, 50.935173) ) CenterLocations ([CenterID], [Lat], [Long]); UPDATE #Users SET [CenterGeography] = GEOGRAPHY::Point([Latitude], [Longitude], 4326) ,[AreaGeography] = GEOGRAPHY::Point([Latitude], [Longitude], 4326).STBuffer([TravelDistance]); INSERT INTO #Locations ([LocationID], [CenterGeography]) SELECT CenterLocations.[CenterID] * 10000 + L.[value] ,GEOGRAPHY::Point(CenterLocations.[Lat] + ABS(CHECKSUM(NEWID())) % 1000 * 0.0001, CenterLocations.[Long] + ABS(CHECKSUM(NEWID())) % 1000 * 0.0001, 4326) FROM GENERATE_SERIES(1, 100) L CROSS APPLY ( VALUES (1, 13.404954, 52.520008) ,(2, 13.737262, 51.050407) ,(3, 6.783333, 51.233334) ,(4, 9.183333, 48.783333) ,(5, 6.953101, 50.935173) ) CenterLocations ([CenterID], [Lat], [Long]); RETURN; -- varint 00 - get locations for one user (the spatial index is used without the hint) SELECT U.[UserID] ,L.[LocationID] FROM #Users U INNER JOIN #Locations L WITH (INDEX = [IX_#Locations_AreaGeography]) ON U.[CenterGeography].STDistance(L.[CenterGeography] ) <= U.[TravelDistance] WHERE U.[UserID] = 10001; -- variant 01 - intersect -- spatial index not used -- 83 seconds DROP TABLE IF EXISTS #TEST; SELECT U.[UserID] ,L.[LocationID] INTO #TEST FROM #Users U INNER JOIN #Locations L ON U.[AreaGeography].STIntersects(L.[CenterGeography]) = 1; -- variant 02 - STDistance -- spatila index is used -- 62 seconds DROP TABLE IF EXISTS #TEST; SELECT U.[UserID] ,L.[LocationID] INTO #TEST FROM #Users U INNER JOIN #Locations L WITH (INDEX = [IX_#Locations_AreaGeography]) ON U.[CenterGeography].STDistance(L.[CenterGeography] ) <= U.[TravelDistance]; -- variant 03 - STDistance -- only works if the <= argument is static but it is executed for 13 seconds DROP TABLE IF EXISTS #TEST; SELECT U.[UserID] ,L.[LocationID] INTO #TEST FROM #Users U WITH (INDEX = [IX_#Users_CenterGeography]) INNER JOIN #Locations L ON U.[CenterGeography].STDistance(L.[CenterGeography] ) <= 6437 --U.[TravelDistance] OPTION (RECOMPILE); The example uses GENERATE_SERIES , so it requires SQL Server 2022. I can create any type of index or materialized any kind of geography object, so one can change the tables definitions if you want. For now, it seems the fastest technique is variant 03 but it requires hard coded travel distance to work, which basically means I am not returning the correct data. I want to know is there a way to optimize such queries using spatial data in SQL Server or I need to find alternative to get better performance (for example to pre-calalculate with triggers the locations in range per each user).

I was getting this error: 'geography::Point' failed because parameter 1 is not allowed to be null. On this sql: SELECT [ID], geography::Point([lat], [long], 4326) AS [loc] FROM ( SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long] FROM ( SELECT [ID], [lat], [long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence] FROM [GEO] ) AS temp1 WHERE [sequence] = 1 AND [lat] IS NOT NULL AND [long] IS NOT NULL ) AS temp2 ORDER BY [ID] But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work: SELECT [ID], [loc] FROM ( SELECT [ID], geography::Point([lat], [long], 4326) AS [loc], ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence] FROM [GEO] WHERE [lat] IS NOT NULL AND [long] IS NOT NULL ) AS temp WHERE [sequence] = 1 I would really like to understand what I was doing wrong in the first query and why this worked in the second query? The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type. In the second query I convert the lat and long data and then filter. I thought I was being more efficient with the first query.

Need help loading point data in sql spatial. Similar to this post but I really dont know how to get XY data to be accessible by sql I want to do what was done in the initial part of this post (Identify points that plot inside the polygon): https://gis.stackexchange.com/questions/166399/how-do-i-find-points-that-are-not-within-a-list-of-polygons-using-ms-sql SELECT points.name, polygons.name FROM points CROSS JOIN polygons WHERE (polygons.shape.STContains(points.shape) = 1) How do you get the points loaded into your table and sql to recognize the points with out having to define them like the below? insert into @points values (geometry::Point(1,1,0)), (geometry::Point(2,3,0)), (geometry::Point(5,4,0)), (geometry::Point(4,2,0)) I am having a hard time defining points without doing it manually like above Ideally I could build a table like this by selecting the xy data I have in a table already

Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images. A dump of the database was taken from the first machine using pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump and then restored on the database on the second machine using pg_restore --clean --dbname=foo /tmp/foo.dump When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates of type Geography contains values that look very different after restoring. **Query Result on 1st Machine (pg_dump source):** SELECT coordinates FROM locations LIMIT 5; **Query Result on 2nd Machine (after pg_restore):** SELECT coordinates FROM locations LIMIT 5; However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map. This makes me think that the data on both machines are the same, but stored in different representation. **Question:** Why did the Geography data values in the column coordinates changed, and how can we restore from the dump while keeping the original data values? ---------- **Update:** Tried using -b when performing pg_dump, but the problem persist. pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump

I posted a question on how to create a relation between 2 tables based on a spatial field https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering The performance is terrible when the city has only 5k records and listing has 12k (takes 54 seconds to run on sql server 2019, dual core i5) Anything I can do to improve it? Execution plan is https://www.brentozar.com/pastetheplan/?id=BkiRk-74P 2nd Plan (based on the query in the answer by Kumar) https://www.brentozar.com/pastetheplan/?id=HJSLs_L4D This is the query select l.*, city.* from listings l cross apply ( select top (1) c.UnicodeName, c.name, r.code as region, cn.code as country from cities c inner join regions r on r.regionid = c.regionid inner join Countries cn on cn.CountryId = r.countryid where c.location.STDistance(l.location) is not null order by c.Location.STDistance(l.location) ) as city Indexes on cities and listings CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings] ( [Location] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings] ( [Location] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO **Updated Query based on Kumar's Suggestion**, which does not bring back any results. it seems this code is assuming that listing and city have the exact same position. They dont, I just want to pick the nearest city for each listing. I also had to change = to STEquals as there were syntax errors otherwise. ;With CTE as ( select l.*, c.RegionId, c.UnicodeName, c.name,c.location.STDistance(l.location) Locationdistance from listings l inner join cities c on l.location.STEquals(c.Location)=1 ) select c.*,r.code as region, cn.code as country from CTE C inner join regions r on r.regionid = c.RegionId inner join Countries cn on cn.CountryId = r.countryid where Locationdistance is not null order by Locationdistance

I am new to the gis world, and my boss wants me to find a way to automate the process to create back-ups on a local SQL Express DB. I know that I can create one for nonlocal DBs, however, I was wondering if there was a way to also automate at the local level. I haven't been able to find any information on how to create this at all, most posts say it's not for local DBs.Is this true?