0

I want to store 2 coordinate points (latitude, longitude) in a table variable.

I have tried:

declare @coordinates table(latitude1 decimal(12,9), 
                           longitude1 decimal(12,9), 
                           latitude2 decimal(12,9), 
                           longitude2 decimal(12,9)) 

select latitude, 
       longitude into @coordinates 
from   loc.locations 
where  place_name IN ('Delhi', 'Mumbai')
select @coordinates

It's showing error:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '@coordinates'.

The result of the select query:

select latitude, 
       longitude 
from   loc.locations 
where  place_name IN ('Delhi', 'Mumbai')

is:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

How can I store the values in table datatype?

I ran the query SELECT @@VERSION and got the result:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 16299: )

6

Use this one:

DECLARE @coordinates TABLE (
    latitude1 DECIMAL(12,9),
    longitude1 DECIMAL(12,9)
) 

INSERT into @coordinates
SELECT
    latitude, 
    longitude
FROM loc.locations 
WHERE place_name IN ('Delhi', 'Mumbai');

SELECT * FROM @coordinates

Note:

  1. You created 4 column with NOT NULL behaviors, but your inserting on 2 columns only. It will return an error.

  2. Use INSERT INTO instead of SELECT INTO. Table is already created.

  3. Use SELECT..FROM when calling DECLARE tables.
  • 1
    You created 4 column with NOT NULL behaviors – actually they didn't. None of the columns is declared with an explicit NULL or NOT NULL, which makes the former the default, so all columns are nullable and there will be no error. I'm not sure why two sets of columns but maybe that's part of the problem (how to populate two sets of columns from two rows) – Andriy M Jan 19 '18 at 8:58
  • @AndriyM, are you sure? If using insert and not specifying the columns, trying to {insert @x select y} will result in a "Column name or number of supplied values does not match table definition" error. – Simon Hellings Jan 19 '18 at 11:58
  • 1
    @SimonHellings: I was commenting specifically on the NOT NULL point raised by Edgar. (Although I have since been slightly corrected on that as well, because apparently the default nullability of columns can be controlled through SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON). Regarding the syntax, yes, you are perfectly correct, when you are not specifying target columns in INSERT, you must supply values for all writeable columns of the target table. – Andriy M Jan 19 '18 at 13:01
  • @AndriyM thanks for clearing that up :) So easy to misinterpret something that is written. – Simon Hellings Jan 19 '18 at 13:05
5

These are Spatial Coordinates, so you should store them with a Spatial Geography Point

CREATE TABLE t (
  pt1 geography,
  pt2 geography
);

INSERT INTO t (pt1,pt2) VALUES 
(
  geography::Point(77.216670000, 28.666670000, 4326),
  geography::Point(72.847940000, 19.014410000, 4326)
);

SELECT  pt1.STAsText() AS pt1, pt1.STAsText() AS pt2
FROM t;

pt1                        pt2
POINT (28.66667 77.21667)  POINT (28.66667 77.21667)

The third parameter (4326) is the Spatial Reference Identifier (SRIDs). "The SRID corresponds to a spatial reference system based on the specific ellipsoid used for ... mapping." SQL Server currently only supports this one value.

Note, if they're direction ie, pt1-pt2 represents a plane-route or something, I would use a Line instead,

See also,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.