sql,ruby,gis,postgis , What is wrong with this ST_CONTAINS statement PostGIS - Find point in polygon


What is wrong with this ST_CONTAINS statement PostGIS - Find point in polygon

Question:

Tag: sql,ruby,gis,postgis

I'm trying the following:

Event.where('ST_Contains(?,ST_SetSRID(location, 4326)::geography)', search_polygon::geography)

add getting the error

*** NoMethodError Exception: undefined method `geography' for

but without that (::geography) I get a message telling me to cast, what do I do?

HINT: No function matches the given name and argument types. You might need to add explicit type casts.


Answer:

It looks like you are trying to use ST_Contains on geography types, but that function only works on geometry types.

If you are OK with the intersects spatial relation (see DE-9IM), then use ST_Intersects(geography, geography).


Related:


mysql_real_escape_string creates \ in server only not in local


php,sql
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...

SQL Multiple LIKE Statements


sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

SQL Group By multiple categories


php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

Query how often an event occurred at a given time


mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

Ruby access words in string


ruby
I don't understand the best method to access a certain word by it's number in a string. I tried using [] to access a word but instead it returns letter. puts s # => I went for a walk puts s[3] # => w ...

How to join result of a Group by Query to another table


sql,tsql
I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

Make instance variable accessible through hash in Ruby


ruby-on-rails,ruby,ruby-on-rails-4,activerecord
In Rails, ActiveRecord objects, attributes are accessible via method as well as through Hash. Example: user = User.first # Assuming User to be inheriting from ActiveRecord::Base user.name # Accessing attribute 'name' via method user[:name] # Attribute 'name' is accessible via hash as well How to make instance variables accessible through...

MySQL: Select several rows based on several keys on a given column


mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Default the year based on month value


sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

Add 1 to datediff result if time pass 14:30 or 2:30 PM


sql,ms-access,ms-access-2007
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

SQL varchar variable inserts question mark


sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

Foreign key in C#


c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

T-SQL Ordering a Recursive Query - Parent/Child Structure


sql,tsql,recursion,order,hierarchy
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...

Inner Join 3 tables pl/sql


mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

left join table, find both null and match value


sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Same enum values for multiple columns


ruby-on-rails,ruby,enums
I need to do something like this: class PlanetEdge < ActiveRecord::Base enum :first_planet [ :earth, :mars, :jupiter] enum :second_planet [ :earth, :mars, :jupiter] end Where my table is a table of edges but each vertex is an integer. However, it seems the abvove is not possible in rails. What might...

Pull information from SQL database and getting login errors


php,sql,database
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

Take thousand value in SQL


sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

How do I find records in one table that do exist in another but based on a date?


mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

Ruby- get a xml node value


ruby,xml
can someone help me in extracting the node value for the element "Name". Type 1: I am able to extract the "name" value for the below xml by using the below code <Element> <Details> <ID>20367</ID> <Name>Ram</Name> <Name>Sam</Name> </Details> </Element> doc = Nokogiri::XML(response.body) values = doc.xpath('//Name').map{ |node| node.text}.join ',' puts values...

Select Statement on Two different views


sql
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...

SQL: overcoming no ORDER BY in nested query


sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

Select count Columns group by No


sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

Convert AWK command to sqlite query


sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

Rails shared controller actions


ruby-on-rails,ruby,ruby-on-rails-4
I am having trouble building a controller concern. I would like the concern to extend the classes available actions. Given I have the controller 'SamplesController' class SamplesController < ApplicationController include Searchable perform_search_on(Sample, handle: [ClothingType, Company, Collection, Color]) end I include the module 'Searchable' module Searchable extend ActiveSupport::Concern module ClassMethods def...

Using Sum in If in Mysql


mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

oracle sql error Case When Then Else


sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Loop until i get correct user


ruby,redis
I have users stored in Redis and want to be able to call only certain subsets from a set, if i don't get the correct user back i want to put it back in the set and then try again until i get one of the desired users @redis =...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument


sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

Heroku RAM not increasing with upgraded dynos


ruby-on-rails,ruby,ruby-on-rails-3,memory,heroku
I have a massive function i have been calling manually through the heroku rails console. I have been receiving the error rapid fire in my logs: 2015-06-22T14:56:42.940517+00:00 heroku[run.9877]: Process running mem=575M(112.4%) 2015-06-22T14:56:42.940517+00:00 heroku[run.9877]: Error R14 (Memory quota exceeded) A 1X dyno is suppose to have 512 MB of RAM. I...

Get unique row by single column where duplicates exist


sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

SQL Repeated Condition in Two Tables


sql,variables,coding-style,condition
I am a beginner to SQL, and I am having trouble getting rid of repeated code. I have a condition "AccountId=20" that I use twice in a SQL query. The SQL looks something like: DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE AccountId=20); DELETE FROM Table_B WHERE...

How to pivot array into another array in Ruby


arrays,ruby,csv
I have a multidimensional array like this one : myArray = [["Alaska","Rain","3"],["Alaska","Snow","4"],["Alabama","Snow","2"],["Alabama","Hail","1"]] I would like to end up with CSV output like this. State,Snow,Rain,Hail Alaska,4,3,nil Alabama,2,nil,1 I know that to get this outputted to CSV the way I want it I have to have output array like this: outputArray =[["State","Snow","Rain","Hail"],["Alaska",4,3,nil],["Alabama",2,nil,1]]...

Retrieve Values As Column


mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

Rails basic auth not working properly


ruby-on-rails,ruby,authentication
I am building a small API that uses basic authentication. What I have done, is that a user can generate a username and password, that could be used to authenticate to the API. However I have discovered that it is not working 100% as intended. It appears that a request...

Get the actual value of a boolean attribute


ruby,page-object-gem,rspec3,rspec-expectations
I have the span: <span disabled="disabled">Edit Member</span> When I try to get the value of the disabled attribute: page.in_iframe(:id => 'MembersAreaFrame') do |frame| expect(page.span_element(:xpath => "//span[text()='Edit Member']", :frame => frame).attribute('disabled')).to eq("disabled") end I get: expected: "disabled" got: "true" How do I get the value of specified attribute instead of a...

How to select next row after select in SQL Server?


sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

On rendering from controller, current_page method does not seem to work


ruby-on-rails,ruby,ruby-on-rails-4,model-view-controller
I have a navigation bar included in application.html.erb. Because for some pages, such as the signup page, I need to place additional code inside the navigation bar, I have excluded those pages for showing the navigation bar through application.html.erb and instead included it in their respective view pages. See code...

Ruby boolean logic: some amount of variables are true


ruby
Let say I have 3 variables: a, b, c. How can I check that just zero or one of them is true?...

Stack level too deep because recursion


ruby-on-rails,ruby,ruby-on-rails-4,twitter
I have a model named Tweet. The columns of the Tweet model are: -id -content -user_id -picture -group -original_tweet_id Every tweet can have one or multiple retweets. The relation happens with the help of original_tweet_id. All the tweets have original_tweet_id nil , whilst the retweets contain the id of the...

Ruby on Rails - Help Adding Badges to Application


ruby-on-rails,ruby,rest,activerecord,one-to-many
I'm creating a rails application that is a backend for a mobile application. The backend is implemented with a RESTful web API. Currently I am trying to add gamification to the platform through the use of badges that can be earned by the user. Right now the badges are tied...

Matplotlib: Plot the result of an SQL query


python,sql,matplotlib,plot
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...

Title search in SQL With replacement of noice words [on hold]


sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

Ruby: How to copy the multidimensional array in new array?


ruby-on-rails,arrays,ruby,multidimensional-array
seating_arrangement [ [:first, :second, :none], [:first, :none, :second], [:second, :second, :first], ] I need to copy this array into new array. I tried to do it by following code: class Simulator @@current_state def initialize(seating_arrangement) @@current_state = seating_arrangement.dup end But whenever I am making any changes to seating_arrangement current_state changes automatically....

SQL Customized search with special characters


sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Can someone explain to me how this statement is an exclude?


sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

What is Rack::Utils.multipart_part_limit within Rails and what function does it perform?


ruby-on-rails,ruby,rack,multipart
Rack::Utils.multipart_part_limit is set to 128 by default. What purpose does the value have and what effect does it have within the Rails system?...

Keep leading zeroes when converting string to integer


ruby
For no particular reason, I am trying to add a #reverse method to the Integer class: class Integer def reverse self.to_s.reverse.to_i end end puts 1337.reverse # => 7331 puts 1000.reverse # => 1 This works fine except for numbers ending in a 0, as shown when 1000.reverse returns 1 rather...

Purging Database - Count purged/not-purged tables


mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...