ST.PointN Vs ST.NPoint

Topics: Developer Forum, Project Management Forum, User Forum
Aug 11, 2008 at 5:46 PM
Hello,

I have to find if a given set of latitude and longitude is present in the given set of Polygon (polygons are in a field  named 'Geometry' in the spatial table ''Intersection_Output_2' )
..........................................................
Here is what I have:

Declare @a float
Declare @b float
Set @a = -93.6603392551339
Set @b = 29.8821835456083

SELECT HUC FROM Intersection_Output_2 where ST.[Contains](Geometry,ST.PointN(@a,@b)) = 1
...............................................................................................................................................

As I did not find any helpful explanation  for the 'Functions' available with the MSSQL Spatial. I just had to guess that the function

ST.Contains would return a bit 1 /0 depending on if  the point is present in the the polygon..

and to make a 'point' using the 'latitude' and 'longitude' available am not sure as to which function I need to use in the list below,

ST.PointN
ST.NPoint
ST.PointFromText.....

am sure there might be other mistakes in the above query.. as this is the 1st time am using this spatial extension to SQL server..If so Please do let me know I would really appreciate it!

Thanks a lot in advance!
-belame

Developer
Aug 12, 2008 at 8:47 AM
Hi belame, you want something like  SELECT HUC FROM Intersection_Output_2 where ST.[Contains](Geometry,ST.Point(@a,@b,@srid)) = 1
srid is the spatial reference for the point which should match the srid of the geometry (or -1 if they are not known)

ST.PointN Returns the N'th point in the first linestring in the geometry., ST.PointFromText creates a point from its well known text. The best reference for the functions is at http://www.opengeospatial.org/standards/sfs which are the standars which mssqlspatial implement. hth jd
Aug 12, 2008 at 3:50 PM
Thanks JohnDiss for the help!

-belame
Apr 22, 2009 at 10:57 PM
I have just finished installing MsSQLSpatial and everything looks fine, I also imported a shapefile with the following columns:
oid
FeatID
CounDist
Shape_Leng
Shape_Area
Geometry
Geometry_Envelope_MinX
Geometry_Envelope_MinY
Geometry_Envelope_MaxX
Geometry_Envelope_Max_Y

I want to find the CounDist that contains a certain Lat/lon
Using the above query as I guide since I came up with :

Declare @a float
Declare @b float
Set @a = -73.919412
Set @b = 40.810291
SELECT CounDist FROM nycc  where ST.[Contains](Geometry,ST.Point(@a,@b,-1)) = 1
which when executed gives a "Cannot find either column "ST" or the user-defined function or aggregate "ST.Point", or the name is ambiguous."
Looking at the function definitions there is no ST.Point function and the function ST.NPoints accepts only one parameter.
I'm thinking that maybe I am misunderstanding how to specify points or misunderstanding the @ewkb and @wkt parameters
Some help would be greatly appreciated I am going through the docs but that is slow going at the moment
Thanks a lot

Kevin


Developer
Apr 23, 2009 at 10:16 AM
Hi Kevin, you should have access to St.Point

you could try running the following against the db..

CREATE FUNCTION [ST].[Point](@x [float], @y [float], @srid [int] = -1)
RETURNS [varbinary](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MsSqlSpatialLibrary].[UserDefinedFunctions].[Point]
 
Apr 23, 2009 at 2:02 PM
Hi John:
I just tried running the Create Function code and got the following error message: "Could not find method 'Point' for type 'UserDefinedFunctions' in assembly 'MsSqlSpatialLibrary'"
Should I try building the assemblies from the source code?

Kevin
Developer
Apr 23, 2009 at 3:23 PM
Hi Kevin, it is definately in the current source so building from source is definately worth a try..
Apr 23, 2009 at 4:32 PM
I rebuilt the source and reinstalled (Had to use the UNSAFE deploy option)  and ST.Point is now there.
Unfortunately the nycc shape file does not work. I tested the functions using a shape file from TIGER/Census and it correctly returned the Congressional District of a given lat/long.
The shapefiles from New York City are intended for use with ArcView and use the Lambert Conformal Conic coordinate system does any of that mean anything to anyone?
Here is a link to the info on the shape file http://www.nyc.gov/html/dcp/html/bytes/meta_dis_citycouncil.shtml
Developer
Apr 24, 2009 at 12:38 PM
Check the Srid is correct for the geometries and there is a matching entry in the spatial_reference_systems table
Apr 24, 2009 at 2:26 PM
John:
I used MapWindow to convert the shapefile from projected coordinates to geographic coordinates and that worked perfectly. I had tried specifying the srid and I got an error message about mismatched srid's, I'll have to retry it to get the exact message. At any rate this project has saved me a great deal of time(Once I found it) and will allow the non-profit I work for to do some things we might not have otherwise been able to. I am going to try to write up my experiences and post in case it will help someone else. By the way for all of us who cannot afford to pay ESRI or MapInfo prices this project and others like it are a godsend. Keep up the good work.

Kevin
Jul 18, 2009 at 5:05 PM
Edited Jan 30, 2010 at 11:49 AM

ST

.Point appears to be the correct sp to use to create a lonlat point. (I think I am using the proper terminology).   I have had the geonames database for a number of years and want to convert the longitude and latitude coordinates over to points (hopefully to take advantage of the spatial indexing (sql 2008).  I've deployed the software (msscmd -deploy -server=mssql_server -db=mssql_database) and would like to take advantage of the ST.POINT.

UPDATE

Alas, after lots of testing, came up with the following... (Sql Server 2008)

alter table mytable add geog geogrphy null

mygeonamedb SET thepoint_lonlat = ST.Point(longitude, latitude, 4269)

UPDATE mytable set geog=geography::STGeomFromText('POINT('+convert(varchar(18),lng)+' '+Convert(varchar(18),lat)+')',4326);