
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



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



Thanks JohnDiss for the help!
belame



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 userdefined 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



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]



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



Hi Kevin, it is definately in the current source so building from source is definately worth a try..



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



Check the Srid is correct for the geometries and there is a matching entry in the spatial_reference_systems table



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 nonprofit 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 6:05 PM
Edited Jan 30, 2010 at 12:49 PM

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);

