topic spatial joining between points and geometry in SAS Programming

spatial joining between points and geometry

umarigann — Thu, 02 Nov 2023 11:00:38 GMT

I have table1 with columns of segment_id, xcoordinate, ycoordinate and I have another table with the columns of segment_id, geometry, wktgeometry. those two tables coming from a SQL databases, My geometries are in char format, my first table contains null values for some values of segment_id. I want to search xcoordinate and ycoordinate inside second table's geometry column to find my missing segment_id in the first table.

How can i achieve this?

I had tried following query but getting error,

Code:

segment_id  xcoor    ycoor
406         28.66968 41.0059921
416         22.66968 41.3059921
426         28.66968 41.7159921
.           31.66968 39.7159921

segment_id, geometry
426 LINESTRING (28.909554 41.027676, 25.909642 41.3276522, 28.911091 41.02725, 28.9128588 41.0269018, 28.912888 41.026896, 28.912951 41.026884, 28.9129567 41.0268827, 28.91296 41.026882)
466 LINESTRING (28.909554 41.027676, 28.909642 41.0276522, 28.911091 41,31221)

proc sql;
    create table fixed_geoms as
    select a.*, b.wktgeometry,  b.segment_id
    from table1 as a
    left join table2 as b
    on b.segment_id = a.segment_id
    where contains(b.wktgeometry, point(a.xcoordinate, a.ycoordinate));
quit;

The error I faced:

ERROR: Function CONTAINS could not be located.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
89   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.39 seconds
      cpu time            0.03 seconds
      
90   
91   
92   %studio_hide_wrapper;
103  
104

Re: spatial joining between points and geometry

ballardw — Thu, 02 Nov 2023 14:40:29 GMT

Provide examples of the tables Table1 and Table2 as working data step code to allow us to create data sets we can test code against. Paste the code into a text box opened on the forum with the </> icon that appears above the message window.

Contains in SAS Proc SQL is looking to see if one sql expression, such as a variable or operation on one or more variables will "contain" as part of the result a specific string value. Example

proc sql;

title 'Most Current Information for Ticket Agents';
   select p.IdNumber,
          coalesce(p2.jobcode,p.jobcode) label='Current Jobcode',
          coalesce(p2.salary,p.salary) label='Current Salary'
      from proclib.payroll p left join proclib.payroll2 p2
      on p.IdNumber=p2.idnum
      where p2.jobcode contains 'TA';

Keeps records in the result with jobcode variable from the data set proclib.payroll2 has 'TA' somewhere in the value.

You have multiple constructs that make me wonder if this is correct part of the forum. I don't find LINESTRING in a search at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/pgmsashome/home.htm

If the linestring is supposed to be map polygon it may be possible to recast this to Proc GINSIDE but I don't have any clue how SQL is supposed to search that linestring and determine a match.

Re: spatial joining between points and geometry

Quentin — Thu, 02 Nov 2023 17:18:49 GMT

@ballardw wrote:

You have multiple constructs that make me wonder if this is correct part of the forum. I don't find LINESTRING in a search at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/pgmsashome/home.htm

If the linestring is supposed to be map polygon it may be possible to recast this to Proc GINSIDE but I don't have any clue how SQL is supposed to search that linestring and determine a match.

I wonder if the linestring data is coming from SQL server? Apparently it has a linestring object... https://learn.microsoft.com/en-us/sql/relational-databases/spatial/linestring?view=sql-server-ver16

Re: spatial joining between points and geometry

umarigann — Mon, 06 Nov 2023 07:47:44 GMT

yes it is coming from a postgrsql. Yes it is a linestring and my question here is to look for coordainates inside those linestring.