This project is read-only.

Intersections

Topics: Developer Forum, Project Management Forum, User Forum
Jul 2, 2007 at 11:16 PM
Hi!, i am wonderfull about this project, congratulations!

I am testing this, i have installed this on my local SQL Server 2005 and load some data from a shape file (street data).
I want find the intersections on some street and i have problem with the intersections(). This is my sql code.

When i run this:

SELECT a.oid, a.Nombre, b.oid, b.nombre, ST.AsText(a.thegeom), ST.AsText(b.thegeom)
FROM CallesUY as a, CallesUY as b
WHERE ST.Intersects(a.TheGeom, b.TheGeom) = 1 and a.nombre = 'EJIDO' and b.nombre = 'COLONIA'

I have this response:
28530 EJIDO 28543 COLONIA LINESTRING(-56.1869950463849 -34.9046298525608,-56.1870975861498 -34.9036785825611) LINESTRING(-56.1869950463849 -34.9046298525608,-56.1861218390467 -34.9045830545671)
28530 EJIDO 28552 COLONIA LINESTRING(-56.1869950463849 -34.9046298525608,-56.1870975861498 -34.9036785825611) LINESTRING(-56.188121491043 -34.9046893563777,-56.1869950463849 -34.9046298525608)
28545 EJIDO 28543 COLONIA LINESTRING(-56.1868910397491 -34.9055721133224,-56.1869155636573 -34.9053421864079,-56.1869950463849 -34.9046298525608) LINESTRING(-56.1869950463849 -34.9046298525608,-56.1861218390467 -34.9045830545671)
28545 EJIDO 28552 COLONIA LINESTRING(-56.1868910397491 -34.9055721133224,-56.1869155636573 -34.9053421864079,-56.1869950463849 -34.9046298525608) LINESTRING(-56.188121491043 -34.9046893563777,-56.1869950463849 -34.9046298525608)


BUT.... when i want to obtain the point of intersection...

SELECT a.oid, a.Nombre, b.oid, b.nombre, ST.AsText(a.thegeom), ST.AsText(b.thegeom),
stintersection(a.thegeom, b.thegeom) AS intersectiongeom
FROM CallesUY as a, CallesUY as b
WHERE ST.Intersects(a.TheGeom, b.TheGeom) = 1 and a.nombre = 'EJIDO' and b.nombre = 'COLONIA'

this is the error msg:

Msg 195, Level 15, State 10, Line 2
'st_intersection' is not a recognized built-in function name.

Please, can you tell me waht is wrong.

Another question is where i can found all function that are implemented on this project.

Thank a lot!
Jul 3, 2007 at 3:08 AM
In MsSqlSpatial, the function name is ST.Intersection (note the dot). All functions start with ST.
You can see all the available functions using SQL Server Management Studio, under "Programmability/Functions" folder. Please, apologize the lack of documentation.

Best regards,
Ricardo Stuven.
Jul 3, 2007 at 4:37 AM
Hi, thanks a lot, i try that.
you sorry my ignorance.... i am newbe onthis so... thank and great work!!!!

Javier
Jul 3, 2007 at 6:16 AM
Ricardo, when i try to view any function i have this error:

thanks
Javier


TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Property AnsiNullsStatus is not available for UserDefinedFunction 'ST.Centroid'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=MicrosoftSQLServer&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=AnsiNullsStatus&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Jul 3, 2007 at 4:33 PM
Try this query:

select 
object_type =
    case o.type 
        when 'FT' then 'Table-valued function' 
        when 'FS' then 'Scalar function'
        when 'AF' then 'Aggregate function'
        when 'P'  then 'Stored Procedure'
        when 'PC' then 'Stored Procedure'
        else o.type_desc
    end,
object_name = 'ST.' + o.name,
parameter_name =
    case when p.parameter_id = 0
        then 'RETURNS' 
        else replace(p.name,'ewkb','geometry')
    end,
parameter_type = t.name
from sys.objects o
    left join sys.parameters p
    on o.object_id = p.object_id
    left join sys.types t
    on p.user_type_id = t.user_type_id
where o.type in ('FT', 'FS', 'AF', 'P', 'PC')
and o.schema_id = (select schema_id from sys.schemas where name = 'ST')

Best regards,
Ricardo Stuven.