CODEWITHSUNDEEP
postgresqlgispostgis
Bexxx
Bexxx

Reputation: 1

Problems with spatial join

I am new to sql, and attempting to use it to speed up spatial analysis on a set of ~1.2 million trips from a csv that contains the lat and lon for pickup and dropoff points. What I am trying to do in plain English is:

The code I am working with is below (If its helpful, NTA or neighborhood tabulation area, is the neighborhood name which I want to display in my table at the end of this operation) :

    --Select all trips that end in project area
SELECT *
INTO end_PA
FROM trips, projarea
WHERE ST_Intersects(trips.dropoff, projarea.geom);

--for trips that end in project area - index by NTA of pick up point

ALTER TABLE end_PA ADD COLUMN GID SERIAL;

CREATE TABLE points_ct_end AS
SELECT nyct2010.ntacode as ct_nta, end_PA.gid as point_id 
from nyct2010, end_PA WHERE ST_Intersects(nyct2010.geom , end_PA.pickup);


--Count most common NTA
--return count for each NAT as a csv
copy(
select count(ct_nta) from points_ct_end
group by ct_nta
order by count desc)
to 'C://TaxiData//Analysis//Trips_Arriving_LM.csv' DELIMITER ',' CSV HEADER;

However, I am having problems from the very start - ST_Intersects does not return any points within the area of interest!

Troubleshooting solutions I have tried thus far:

My first thought is that the points weren't in the correct SRID. When I created the 'dropoff' point I set the SRID to 4326. I tried both using ST_SetSRID to change the projection of both data sets to 4326, and manually re projecting the shapefiles to 4326 in ArcMap - but neither worked.

I plotted a small sample of the points from the 'trips' data set in Arc Map to ensure they were correctly projected and overlapping with the ProjArea shapefile. They are.

I imported the multipoint shapefile this created into my geo database to test if that worked with ST_Intersects. Nope.

I tried using ST_Within. This threw the error message:

ERROR: function st_within(character varying, geometry) does not exist
....
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I am using Big SQL and postgres Thanks!!

Upvotes: 0

Views: 595

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

My first thought is that the points weren't in the correct SRID. When I created the 'dropoff' point I set the SRID to 4326. I tried both using ST_SetSRID to change the projection of both data sets to 4326, and manually re projecting the shapefiles to 4326 in ArcMap - but neither worked.

ST_SetSRID doesn't change the projection (reproject). It just changes the internal representation. This can totally screw everything up if the previous SRID matched the input data. You likely wanted ST_Transform().

There isn't enough information here to trouble shoot this problem. However, we can answer this...

ERROR: function st_within(character varying, geometry) does not exist

This simply means the first argument is not a geometery. Of course, we can't do anything with that at all because we don't have your query that you tried with ST_Within().

Your syntax for ST_Intersects() looks to be right. But, there simply isn't enough information provided to help. Show some schema and sample data.

Upvotes: 0

Related Questions