I really like the ESRI API's (much faster than Socrata). This is a crazy url, but they do allow for different SQL operations (including spatial operations) right in the API. Here is top 10 crime street segments in Raleigh for example:

https://services.arcgis.com/v400IkDOw1ad7Yad/ArcGIS/rest/ser...

If you go to https://services.arcgis.com/v400IkDOw1ad7Yad/ArcGIS/rest/ser... you can putz around in the query engine to figure out the API strings (easier than reading the ESRI docs anyway!)